Using DoCmd.TransferDatabase - Access Adding a 1 to my Table Name??

[kjv1611]

I'm attempting to use the following code:

     DoCmd.TransferDatabase acImport, "ODBC Database", _
        "ODBC;DSN=MySqlServer;Description=MySqlServer Sandbox;Trusted_Connection=Yes;" & _
        "DATABASE=Sandbox;", acTable, Environ("UserName") & "].[MySandboxTable]" _
        , "Here"

To import a SQL Server table into an Access database. Well, it should work, I suppose, but it's doing one really odd thing.

At every attempt to run, I'm getting this error:

Run-time error '3011':

The Microsoft Office Access database engine could not find the object 'Here1'.
Make sure the object exists and that you spell its name and the path name correctly.

Well, I used "Here", not "Here1" in my testing, but it's seeing it as "Here1"...
Could this be a form of data corruption somewhere, or is it just giving me a non-meaningful error message when something else is actually my issue?

Thanks for thoughts or suggestions

 

[kjv1611]

Additional Info: Using MS Access 2007, particular server is currently on MS SQL 2005... will be MS SQL 2008 beginning next Monday - though surely that doesn't matter for this.

Also, I just tried creating a new database, running the same short script, and got the same error message - so I doubt it's corruption.

 

[kjv1611]

Okay, another piece...

I initially had the issue, but didn't notice the name.. so I figured, since it said it couldn't find it, I'd just create the table, and then run the code... didn't make sense that it had to be there first, but thought I'd try...

So, that's why it's adding a 1 to the table name.

If I give it a table name that doesn't exist, it doesn't add the one, but otherwise gives the exact same error...

I just don't get it.. it says "can't find this object"... well, duh, I'm asking you to create the object. Of COURSE it's not there...

Oh well, I'll dig around a little more and see if I can find the answer. If anyone has a suggestion, I'm all ears.

Thanks.

 

[kjv1611]

Hmm... if I change it from acImport to acLink, it says it can't find the SQL table instead of the access database table destination...

 

[kjv1611]

Never found an answer to this one that seemed to fit. However, I got around it with another passthrough query. If anyone still responds with an answer, I'd be curious to know what it is.

Thanks.

 

[vbajock]

Access appends a "1" if a table of the same name already exists in the database.

 

[kjv1611]

I'm obviously not dealing with this issue any longer, but just got to thinking about it... I wonder if this issue was caused by data corruption in the database table schema?

I realize that Access adds a 1 to a table if you use the same table name... but in my case, the table DID NOT EXIST when it added the 1 to the name. So apparently, Access thinks it exists, which is why it's adding the name. So either some data got corrupted in the MS..whatever "tables" able or else something else was going on... which I think points to corruption regardless. I may have to look back at the database later, dig into the system tables, to see if I can find the answer there... maybe. At this point, it's just a curiosity, don't know that I'll have time to find the real answer.