asked on

DoCmd.TransferDatabase - need assistance with syntax. keep receiving runtime 13 type mismatch error

Hello,

I have been trying to get this working now for a while but not having any success.... If someone can take a quick look and provide some insight I would really appreciate it.

What I am trying to do is automate the importing of a table from an Oracle database to my local access database.
When running the following code, I receive runtime error 3151 ODBC Connection to 'GetPaid_7i.dsn' failed.

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i.dsn;UID=XXXX;PWD=XXXX;LANGUAGE=us_english;", acTable, "GPCOMP1_GPCUST", "TEST"

I have verified that the user name and password is correct. Please let me know if you can assist. Thanks!

expression.Transferdatabase(transfertype, databasetype, databasename, objecttype,source, destination, structureonly, storelogin.

rbrooker

Hi,

check your DSN's, there is one called GetPAIN_7i? or GetPAID_7i.dsn? try leavving off the ".dsn" and try again...

good luck :)

lorincha

ASKER

Thanks rbrooker. I tried removing the .dsn but unfortunately it didn't work. Also, it the dsn is named correctly. Thanks for the quick response. If you or anyone can think of other suggestions, please let me know.

lorincha

ASKER

it didn't work - means same error msg / result

rbrooker

is there a username and password hardcoded into the dsn?

lorincha

ASKER

No, it's only there in the vba code. When I use the database itself, I am prompted for username and password. The usrname and password that is in the vba code logs in successfully.

SOLUTION

rbrooker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lorincha

ASKER

ok, thanks rbooker. I raised the point value... if anyone can help out... I've been stuck here for a while and need to get this working. any insight is appreciated greatly....

lorincha

ASKER

I Am still receiving the same error......"runtime error 3151 ODBC Connection to 'GetPaid_7i' failed.

I tried changing the command as follows. Notice I left out "database" but the error still persists.... Please if any one can assist I would greatly appreciate this one...... I can't figure out what the issue is....

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english;database=GPCOMP1", acTable, "GPCUST", "TEST"

lorincha

ASKER

I have verified the username and password is correct.

SOLUTION

Imoutwest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Data-Man

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lorincha

ASKER

Thanks everyone for the responses. Still not working though .... Here is what I've tried so far and some additional information.

- I am using Microsoft Oracle Driver
- The DSN that is set up works properly (using same usrname and password as specified in my string). I can link and import tables tables successfully using File --> Get External Data --> Select ODBC
- I also tried removing the password.
- using ODBC Database, instead of ODBC

I found that the DSN that was created is located on my hard drive: c:\OraHome\network\ADMIN\GetPaid_7i.dsn

Contents of DSN file show as follows:
[ODBC]
DRIVER=Microsoft ODBC for Oracle
UID=gpread
SERVER=cisprd33

Files in this directory include:
names.ora
sqlnet.ora
tnsnames.ora

Below are the different errors I've received trying many different combinations. Please see below.

ERROR: Run-time error '2507' The odbc database type isn't an installed database type ofr doesn't support the operation you chose
ERROR: Run-time error '3151' ODBC--connection to 'GetPaid_7i' failed.
ERROR - Run-time error '3170' Could not find installable ISAM.

Here are the various attempts I've made organized by ERROR received.....

ERROR: Run-time error '2507' The odbc database type isn't an installed database type ofr doesn't support the operation you chose

DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1_GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1.GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1_GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1_GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1.GPCUST", "TEST"
oCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1.GPCUST", "TEST"
DoCmd.TransferDatabase acImport, "odbc database", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1.GPCUST", "TEST"

ERROR: Run-time error '3151' ODBC--connection to 'GetPaid_7i' failed.

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1.GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; server=cisprd33", acTable, "GPCOMP1_GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1_GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC;dsn=GetPAID_7i;UID=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"

ERROR - Run-time error '3170' Could not find installable ISAM.

DoCmd.TransferDatabase acImport, "odbc", "ODBC Database;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1_GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC Database;dsn=GetPAID_7i;UID=XXX;PWD=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"

DoCmd.TransferDatabase acImport, "odbc", "ODBC Database;dsn=GetPAID_7i;UID=XXX;LANGUAGE=us_english; database=gpcomp1", acTable, "GPCOMP1.GPCUST", "TEST"

rockiroads

With this

"odbc database",

I dont know if its case sensitive, I have used it before and used "ODBC Database"

Have u tried creating a new DSN but based on the Oracle driver and not the Microsoft one and to see what it does?
The oracle one is better as it gives more meaningful error messages, so u may get a proper msg here