Re-Connecting via ODBC

  • Thread starter Thread starter Chris Rettke
  • Start date Start date
C

Chris Rettke

I have a bit of experience with ODBC connections but not
much with Modules/VBA. I have an ODBC link to a table
that I periodically re-make via a SQL Query tool.
Basically I drop the table then re-make it with the same
name (new data). Problem is that I have a link to this
table in Access 2000 and everytime I drop and re-make the
table my link gets busted. Next time I re-open table in
Access I get all #Deleted. Is there a way I can write a
procedure to delete the linked table, then re-link and
change the name of the linked table? Would like to run at
beginning of an update macro without having to manually
delete and re-link.

Thanks in advance if anyone has any ideas.

c-
 
I have linked tables to Oracle and in code drop the link
and rebuild it to get by the #Deleted. The code I use is
DoCmd.DeleteObject A_TABLE, 'tablename'
and
DoCmd.TransferDatabase A_ATTACH, "<SQL
Database>", "ODBC;DSN=" & 'ODBCDataSource' & ";UID="
& 'UserName' & ";PWD=" & 'Password' & ";DBQ="
& 'ConnectString' & ";ASY=ON;", , txtUserName & "." &
ucase('tablename'), 'tablename', False, 'true/false'
 
Back
Top