Link to Oracle table via VBA

[teel73]

I am trying to link to oracle tables using VBA. I can link to the oracle tables manually but when I try to use the TransferDatabase method it doesn't work. Here is my code:

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=C:\Users\lewis.tarsha\Desktop\AutomationTool\ODBCs\ICRTPP;UID=RPT_GROUP;PWD=RPT_GROUP#1;SERVER=ICRTPP", acTable, "OFLC.STATUS", "Access_Status", False, False

When I run this, I get a run-time error 3151; ODBC connection failed.

I am running MS Access 2010. Can someone please tell me what I'm doing wrong?​

 

[DavidAtWork]

I think it's the DSN that's causing the problem, instead of the C:\Users... just enter the name of the DSN that you gave it when you set it up on the machine (look in your ODBC manager)
Failing that a better way would be to use a DSN-less connection string

David

 

[Rx_]

I am pretty sure it is version dependent for both sides.
For a project, I was able to download the Oracle driver and link Access via odbc.
Eventually, was able to use SQL Server Linked Server (this is absolutely prefered).

Now, Oracle is one of the worst of the worst sites for support or documentation for anything related to Microsoft. That said, the Microsoft docs and tools are worse than Oracle's. When I was going through this very fustrationg process, Microsoft announced it would "work to support Oracle". That was just in my opinion the marketing arm making pie-crust claims (easily broken).
The support for these two companies is about as realistic as the mid-east peace talks.
I highy suggest backing up your Registry and important files before installing the Oracle driver. For gosh sake, don't install it twice! It doesn't un-install as they claim. And that sets up a failure point for the 2nd install.
I located some non MS non Oracle sites with suggestion on the web that were many times more informative.
So, bottom line, read everything in Oracle, download the java tool.

Search for my post on this forum, I vaguely remember leaving some useful sites.
B.T.W. I think they only have a 32 bit tool for Windows.
Do your research. Once it worked, it was great. Until then, it was a huge time-sync.

 

[vbaInet]

If you're trying to create a link to an oracle table then you want to use the TableDefs collection and append the newly created tabledef to the TableDefs collection.

I'm hoping the connection string is the one you got from the Description field after manually creating it?

Why are you needing to do it programmatically anyway?

Edit: Super late at replying.

 

[teel73]

vbaInet, because having to relink 20 tables when the location of the source has moved is a pain and very time consuming.

 

[vbaInet]

Ok, you still use the Tabledefs collection. What about the connection string, where did you get it from?

And it sounds like a one-off exercise. I don't imagine they move it often or do they?