Solved

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

Posted on 2006-11-01
14
2,582 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.
 
 
 
0
Comment
Question by:lorincha
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 18

Expert Comment

by:rbrooker
ID: 17852845
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 :)
0
 

Author Comment

by:lorincha
ID: 17852896
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.
0
 

Author Comment

by:lorincha
ID: 17852900
it didn't work - means same error msg / result
0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17852914
is there a username and password hardcoded into the dsn?
0
 

Author Comment

by:lorincha
ID: 17853052
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.
0
 
LVL 18

Assisted Solution

by:rbrooker
rbrooker earned 100 total points
ID: 17853119
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 :)
0
 

Author Comment

by:lorincha
ID: 17853366
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....
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:lorincha
ID: 17918975
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"
0
 

Author Comment

by:lorincha
ID: 17918988
I have verified the username and password is correct.
0
 
LVL 7

Assisted Solution

by:Imoutwest
Imoutwest earned 50 total points
ID: 17919337
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
0
 
LVL 18

Assisted Solution

by:Data-Man
Data-Man earned 150 total points
ID: 17919513
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
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 200 total points
ID: 17920205
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"
0
 

Author Comment

by:lorincha
ID: 17932687
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"




0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17932989
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
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now