We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

lorincha
lorincha asked
on
Medium Priority
2,722 Views
Last Modified: 2007-12-19
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.
 
 
 
Comment
Watch Question

Top Expert 2006

Commented:
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 :)

Author

Commented:
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.

Author

Commented:
it didn't work - means same error msg / result
Top Expert 2006

Commented:
is there a username and password hardcoded into the dsn?

Author

Commented:
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.
Top Expert 2006
Commented:
well, thats me out, if you can log in through the odbc connection successfully using the same username and password, and the dsn name in the connect string is ok, that was all i had :)

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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....

Author

Commented:
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"

Author

Commented:
I have verified the username and password is correct.
I'm not familiar with Oracle, however, have you tried removing the Database password?

In the past I had difficulties because of the Password between Access databases, may be similiar for Oracle to Access.

Just a thought, best of luck with this project, Imoutwest
Commented:
can you use the DSN to link a table....

File | Get External Data

Select ODBC, and see if you can connect to a table that way...at least that will validate that the DSN works.

Mike
CERTIFIED EXPERT
Top Expert 2006
Commented:
Which Oracle driver are you using?

are you using Microsoft Oracle Driver or Oracle's driver itself
It the latter, then u can test your DSN via the ODBC control panel applet
if u can get yur connection going here then you know your TNS Names entry is fine

Another thing, try using "ODBC Database" instead of "odbc" as the third parameter
try changing database= to server=, does it help any?
also try specifying the schema owner in front of the tablename - u may find u have a synonym for that table and u cant import them


Dim sConn as String

sConn = "ODBC;DSN=GetPAID_7i;UID=XXX;PWD=XXX;SERVER=GPCOMP1"

'or if SERVER= dont make a difference, go back to using DATABASE=
sConn = "ODBC;DSN=GetPAID_7i;UID=XXX;PWD=XXX;DATABASE=GPCOMP1"


DoCmd.TransferDatabase acImport, "ODBC Database", sConn, acTable, "schema_name.GPCUST", "TEST"

Author

Commented:
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"




CERTIFIED EXPERT
Top Expert 2006

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.