0

In Access vba, I have established a connection with Oracle database via ODBC as below.

Dim adConn As ADODB.Connection
Dim adRs As ADODB.Recordset
Dim myDSN As String

Set adConn = New ADODB.Connection

myDSN = "Driver=Oracle in instantclient_11_2;DBQ=111.111.111.11:1521/orcl;UID=xxxxx;PWD=xxxxx;Persist Security Info=True"

adConn.Open myDSN

And I can import table by following External data,ODBC database... But I am not able to import table in VBA with following codes. The error message is 'failed to connect with myDSN'. Can anyone help? Thanks

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=myDSN", acTable, "TableName", "TableName", False
davidzxc574
  • 471
  • 1
  • 8
  • 21

2 Answers2

1

I tried a few solutions and the script below works now.

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=64oracle", acTable, tableName, tableName, False

The problem is that the string variable myDSN below is not the DSN I set up in my ODBC. The DSN should be found at ODBC setup

myDSN = "Driver=Oracle in instantclient_11_2;DBQ=111.111.111.11:1521/orcl;UID=xxxxx;PWD=xxxxx;Persist Security Info=True"
davidzxc574
  • 471
  • 1
  • 8
  • 21
0

That's a strange looking connection string, but I guess that doesn't mean it's wrong if your Server IP/Port is actually 111.111.111.11:1521 (or if you masked it intentionally!)

You can check the setup of your Oracle ODBC DSN (Data Source Name) like this:

  • Hit the Windows KeyWindows Key and start typing ODBC to open: Data Sources (ODBC).

  • Under one of the first 3 DSN tabs (User, System or File) you should have a DSN listed called Oracle in instantclient_11_2 (based on your question).

  • If it's not listed, you will need to create it. If it is listed, you can double-click it and confirm the configuration. If the UID & PW are listed there, then you shouldn't be specifying it in your myDSN string.

  • Click Test Connection (or something similar) to test the data source. (The exact window setup will vary depending on the company & version.)

Another troubleshooting step is to ping the server:

  • Hit the Windows KeyWindows Key, type cmd and hit Enter

  • In the Command Prompt window that opens, type ping 111.111.111.11 and hit Enter.

  • The response should give some indication whether you can connect to the server from your machine.

Also I'm not sure that /orcl should be part of your myDSN connection string. Where did you get that string? Has it worked elsewhere? Any further assistance would require more information, such as the results of these tests, and perhaps a screenshot of the ODBC DSN setup.

More Info:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • Hi thanks for the answer. As I said, ODBC was properly set up already and tested as a success to link Oracle database. In Access I could even import table by clicking 'External data '->'ODBC database'->.... But I dont know how to code this import in VBA – davidzxc574 Feb 10 '18 at 08:41
  • Alright, you hadn't mentioned that it tested successfully after you had the error. – ashleedawg Feb 10 '18 at 08:42
  • What about my UID/PW suggestion? Also see [this related answer](https://stackoverflow.com/a/42826233/8112776). – ashleedawg Feb 10 '18 at 08:44