Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

**basubdd** · 06-29-2013, 06:43 AM

Hi Folks,

I am getting "Error writing DB record: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name".

Really finding it difficult to solve cause the same query runs when i put the code in debug mode or run it for the second or third time. I guess there is something wrong with the Connection. But i have tried everything possible but failed to resolve this issue.

The error say that the query is wrong but the same query runs when i try it for the second or third time in debug mode. Also the query to run in excel the table name has to be concatenated with a '$' sign. Just to give a background of what i m trying to achieve here is:

I am querying few tables to fetch some data based on some criteria and then i am writing these to a flat file.And then later on these flat files are FTP'ed to a mainframe server and then loaded on to ADABAS (Mainframe DB).

Code which is throwing error:

Please Login or Register  to view this content.

Now the above code snippet is a part of the big chunk of code where the "strRecordType" = ELRL and the workbook already have a sheet named "AGE_ELRL".

Any help would be appreciated.

Regards
Basu

**jayj@FIS** · 07-02-2013, 11:02 AM

I found that the connection for the report set had a filename in the connection string. If the name you are referring to was added dynamically and the file has not been saved, it will not exist in that file until a save has been performed. Do a file save before running the rs.Open.

**basubdd** · 07-02-2013, 02:31 PM

Hi Jay,

Thanks a lot for your reply.

Just wanted to know which file are you talking about. Is it AGE_ELRL?

It would be great if you can elaborate a bit more when you say to perform a file save before the rs.Open.

Many thanks in advance.

Regards
Basu

**jayj@FIS** · 07-02-2013, 03:23 PM

The connection object "cnn" used in your rs.Open statement has a connection string associated with it. You didn't provide that part of your code, but I expect it is something like :
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.fullname _
& ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"

The connection string includes the full path to a file, and your query is referring to that file when it runs. If that file has changed to contain the sheet name you are trying to query, you need to save the file so that filename on the disk has that data in it before the query will be successful. In other words, if you just added a sheet named "AGE_ELRL" through code, it's only in memory and not back on the disk, so quering that file from disk won't find a sheet named "AGE_ELRL".

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It also appears the syntax of the other queries might not be correct. I think you need to have the square brackets arounds the sheet name like this:
rs.Open "SELECT * FROM [SheetX$] ", cnn

Hope that helps!

**basubdd** · 07-03-2013, 03:34 AM

Hey Jay,

First of all thanks for your prompt response.

Secondly, the connection string is included in the module section and it somewhat reads like:

Please Login or Register  to view this content.

Now the chunk of code which you are saying to be included; has to be included in the module where i have the OpenDB() and closeRS() sub or from the form which i have mentioned earlier.

Lastly, the square brackets are included in the query. Its just that I'm not mentioning them again. The query which is executed is:

strSql = "SELECT * FROM [AGE_" + strRecordType + "$] order by [POSTTOWN_CODE] asc;"

the AGE_strRecordType will have different values as the strRecordType are nothing but the sheets which I am reading from my excel.

Could you please point me out where to place the 3 lines of code which you have indicated as the connection string which i am using is also a bit different to what you have pointed out.

Regards
Basu

**JapanDave** · 07-03-2013, 04:00 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

**basubdd** · 07-03-2013, 05:36 AM

Hey, sorry to miss the #tag CODE. Included it now.

**Kyle123** · 07-03-2013, 05:58 AM

You shouldn't use ADO to query the open workbook, it causes memory leaks. You can work round it using DAO, or even better AutoFilter

**basubdd** · 07-03-2013, 07:28 AM

Hey Kyle,

Can you please tell me how to use the AutoFilter?

Regards
Basu

**basubdd** · 07-04-2013, 07:39 AM

Made changes to incorporate the file save before opening the recordset. The file save code has been applied where i m writing in the file. The code related to the same is given below:

Please Login or Register  to view this content.

Also the same 3 lines has been added on Form Load event as well.

But it seems that the code is still not working and throwing the same error.

Could you please provide an alternative.

**Kyle123** · 07-04-2013, 07:56 AM

Sure, could you upload a sample file please

**basubdd** · 07-04-2013, 02:00 PM

Originally Posted by jayj@FIS

The connection object "cnn" used in your rs.Open statement has a connection string associated with it. You didn't provide that part of your code, but I expect it is something like :
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.fullname _
& ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"

The connection string includes the full path to a file, and your query is referring to that file when it runs. If that file has changed to contain the sheet name you are trying to query, you need to save the file so that filename on the disk has that data in it before the query will be successful. In other words, if you just added a sheet named "AGE_ELRL" through code, it's only in memory and not back on the disk, so quering that file from disk won't find a sheet named "AGE_ELRL".

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True

It also appears the syntax of the other queries might not be correct. I think you need to have the square brackets arounds the sheet name like this:
rs.Open "SELECT * FROM [SheetX$] ", cnn

Hope that helps!

Hi Jay,

I tried the approach you mentioned to save the files before rs.Open but it seems that this is not working. I have added the updated code for you to look at as well in my last update.

Could you please look into the same and tell me what is actually missing in that code.

Regards
Basu

**basubdd** · 07-04-2013, 02:07 PM

Originally Posted by Kyle123

Sure, could you upload a sample file please

Hi Kyle,

The sample file is actually a huge chunk but the code is actually working sometimes and fails sometimes. It is not like that it fails everytime. If you want I can provide you the demo version of what I am trying to achieve. But that involves lots of steps to reach to that process. Also, there are lots of tables involved in the excel.

One more thing I want to mention here is: The code to create the flat file after reading from the tables (worksheets) is taken as is from the MS Access VBA code. Actually we are trying to migrate our process from Access to Excel with some extra features. The code works perfectly fine in Access but fails sometimes in Excel.

Do you want me to upload the whole package which I am working on?

Regards
Basu

**basubdd** · 07-06-2013, 11:34 AM

hey guys anyone with any clue cause this is getting really tough for me now to solve this issue.

Any help would be greatly appreciated!!!

Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

LinkBack

Thread Tools

Rate This Thread

Display

Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Re: Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions