MS Access ODBC Connections

[mlr0911]

Hello all, is there a way to change an ODBC connection in MS Access. I have 4 ODBC connections that I can choose from (all of them from the same server), and instead of manually recreating a new ODBC connection everytime, I would like to point it to the new connection. The ODBC connections (Tables) have the same information, but throughout different times during the month.

Thanks in advance for your help.

 

[namliam]

Yes there is. The ODBC link is a property of the linked table or query.

check out tabledef and querydef in the access help there you will find examples how you can manipulate them...

 

[mlr0911]

I tried looking for this without any success. Any other suggestions?

Thanks again for your help.

 

[KenHigg]

Yes there is a way to update / change the odbc connect via code using .connect property. Sorry I don't have time to give an example right now...

 

[namliam]

From memory, I hope it doesnt fail me

Currentdb.tabledefs("QueryName").Connect = "ODBC connect string"

You maybe have to do .RefreshLink in order for it to work...

 

[mlr0911]

I have tried the following without any success. I am trying to connect to a cache database using an ODBC connection. The following code is being used:

Dim ChangeODBC As String

ChangeODBC = "ODBC;DSN=Cache Database;server=Test;port=1792;database=test"

CurrentDb.TableDefs("tblimport") = ChangeODBC


Can anyone shed some light on what I am missing or doing wrong? Should I include the driver information here as well? This is the first time that I have tried to do this.

Thanks again for your help.

 

[WayneRyan]

Mlr,

This is just from memory, but should be close though:

Dim ChangeODBC As String
Dim tdf As TableDef

ChangeODBC = "ODBC;DSN=Cache Database;server=Test;port=1792;database=test"

Set tdf = CurrentDb.TableDefs("tblimport")
tdf.Connect = ChangeODBC
tdf.RefreshLink

Wayne

 

[mlr0911]

Thanks for your reply Wayne. I tried your code, but I am getting an error message stating that the Object variable or With Block variable not set; this is referring to the tdf.refresh link code.

 

[WayneRyan]

Mlr,

Well, you're obviously opening/setting the tdf properly.
You have no problem setting the .Connect string.

I'm quite sure that tdf.RefreshLink is what you need, what did the
"intellisense" provide if you just type "tdf.R"?

Also, you might want to explicitly declare:

Dim tdf As DAO.TableDef

Sorry, but I don't even have any Access DBs at this office.
Will check in later.

hth,
Wayne

 

[boblarson]

You might try changing to this:

Dim ChangeODBC As String
Dim tdf As TableDef
Set tdf = New DAO.TableDef
ChangeODBC = "ODBC;DSN=Cache Database;server=Test;port=1792;database=test"

Set tdf = CurrentDb.TableDefs("tblimport")
tdf.Connect = ChangeODBC
tdf.RefreshLink

I don't use DAO much myself so I'm just going off the assumption that some things translate between ADO and DAO (I know, not much) and you get the same error if you don't initialize the variable first, even though the Set tdf = CurrentDbTableDefs("tblImport") would seem to me that it is initializing.

 

[pbaldy]

You may be running into a quirk that requires a variable instead of CurrentDb here. You'll get an error without it.

 

[mlr0911]

Thanks Wyan and Bob for all your help;
I am now receiving the following error on the following

tdf.Connect = ChangeODBC
tdf.RefreshLink


I am getting a Run-Time error '3420'; Object invalid or no longer set.

Any ideas?

Thanks again.

 

[pbaldy]

Already answered:

You may be running into a quirk that requires a variable instead of CurrentDb here. You'll get an error without it.

 

[mlr0911]

pbaldy, thanks for your reply, I didn't see yours when I replied...........Could you provide an example.

Thanks again.

 

[pbaldy]

2 new lines in the appropriate places:

Dim db As DAO.Database
Set db = CurrentDb

and a tweak to this one:

Set tdf = db.TableDefs("tblimport")

 

[mlr0911]

Ahh, looks like it is working now.....I am getting an ODBC--connection to test failed........this is probably due to my ODBC connection string....I will research further...

Thanks alot to everyone to has helped.

 

[pbaldy]

May find help here:

http://www.connectionstrings.com/

or copy from a working linked table.

 

[mlr0911]

Ahh, looks like it is working now.........I am getting an ODBC error, but that is probably due to my connection string.........I will research further.......thanks for your help.

Thanks to everyone.

 

[mlr0911]

That worked Paul...........thanks alot.

 

[namliam]

I have never run into that quirk... I didnt even know it excisted....