DoCmd.TransferDB Settings. 1

[mikeyb540]

I am so puzzeled about this one.

I have a Command_click with the following:
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=JRNL", acTable, "UNNAMED", Text4

When I click to execute it does what it suppose to.
When I change the DSN to a different Table it still imports the old one.

If I exit out completly from MS-AccessXP the new settings apply.

What is up with that?
How can I clear MS-Access Buffer or whatever to pick the new settings?

Pls help.

Txs in advance.

Mike

 

[mikeyb540]

Any ideas.

Pls let me know

Txs

 

[Quehay]

You said: When I change the DSN to a different Table it still imports the old one.

Just to clarify...are you changing the DSN entirely or using same DSN and targeting a different table?

If it's just a table change, maybe making the table into a parameter would keep Access from doing whatever low-level binding that it's doing and create a fresh link each time. This wouldn't do a thing for the DSN change, though.


Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.insightdataconsulting.com

 

[vbajock]

There was another thread with a similiar problem:

thread705-703552

If you are using openlink, the problem could be there, or perhaps we've found a bug in XP.

 

[mikeyb540]

Jeffrey & VBjock,

Thanks for the feedback.

I just change the table in the DSN.

I tried closing and reopening the form.
EXIT SUB after the DoCMD
But I still get an import for the old table.

The only way I can make it work is by closing Access completely.

I noticed one thing in the MSSysObjects. There is an entry there that says temp. After reopening the MDB it is gone but while in the existing session seems that entry locks it.

Is there a way I can delete it from the MSysObjects?

Mike

 

[smuckers]

Good Morning mikeyb540!

I have experienced similar issues and handled them 2 different ways. Each time my objective was to force a change in the link/connection causing it to reset itself.

Sorry, this is not good programming methodology but these two techniques have proven themselves.

My first technique:
1. The first step is to look at the arguments of the command statement. In this case the "Transferdatabase" arguments needs to be evaluated.

2. I then start changing the argument values

3. In your case, changing the table destination name did not work, so I would start if possible with the changing the acImport argument to acExport and export a harmless table or file to the database or to a different database.

4. Now, after that has been performed and verified, change the arguments to the desired settings and it should work for you.

5. To summarize the first technique, the resetting is done by making a big enough change that the "Transferdatabase" command resets itself.

The second technique:

1. In the body of the code, change the arguments enough to cause an error, like putting in "" into one of the required arguments.

2. Trap the error.

3. Now, code and input the correct arguments while processing the error code subroutine and re-run the command and follow it up with resume for the next table import. The resume will take it back to the code body and create another error, thus enabling you to process the next table.

4. Hopefully, causing the error each time will reset the command and you can toggle back and forth.


Finally, it seems the memory is not clearing itself. Perhaps putting the code into a class module and calling it from a standard module for each new table name will work? This way, the class's events, initiate and terminate, will occur each time and theorectically, clear the memory. Even if the memory does not completely clear, a new instance(copy) of the class will still be created which is separate from the previous one and the command should(?) be ready to use.

Good luck and I hope at least one of these ideas work.

Sincerely,

Smuckers

 

[mikeyb540]

Txs Smuckers. I will try the Module solution. Trapping the error? there is no error just imports the old table. And i did check if the SetStringValue changed the registry key.

here's the cmd_click:

Dim Parm as String
Parm = Me!Text4
Me!Check10 = "Yes"
SetStringValue "HKEY_LOCAL_MACHINE\software\ODBC\ODBC.INI\JRNL", "DBQ", Text6
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=JRNL", acTable, "UNNAMED", Parm

DoCmd.RunSQL "INSERT INTO OCTOBER ( [DATE], STORE_NO, REG_NO, DRAWER, TILLID, TX_NO, SEQ_NO, CLERK, [STRING] )" & _
"SELECT DATE, STORE_NO, REG_NO, DRAWER, TILLID, TX_NO, SEQ_NO, CLERK, STRING FROM " & Parm
DoCmd.DeleteObject acTable, Parm


It is a bit crazy but it does what i need it to. I had to import 388 TPS(topspeed files) and it was driving me nuts going at it one by one.

Txs all again for ur feedback