[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2664
  • Last Modified:

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.
 
 
 
0
lorincha
Asked:
lorincha
  • 7
  • 3
  • 2
  • +2
4 Solutions
 
rbrookerCommented:
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
 
lorinchaAuthor 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.
0
 
lorinchaAuthor Commented:
it didn't work - means same error msg / result
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rbrookerCommented:
is there a username and password hardcoded into the dsn?
0
 
lorinchaAuthor 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.
0
 
rbrookerCommented:
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
 
lorinchaAuthor 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....
0
 
lorinchaAuthor 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"
0
 
lorinchaAuthor Commented:
I have verified the username and password is correct.
0
 
ImoutwestCommented:
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
 
Data-ManCOOCommented:
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
 
rockiroadsCommented:
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
 
lorinchaAuthor 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"




0
 
rockiroadsCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now