Access, Oracle, and things that go bump in the middle of the night...

[THoey]

I have an MS Access database (97, but going to be moved to 2K) and an Oracle database. I am trying to write some scripts that will allow an Access table to be populated automatically during the night with data from a duplicate Oracle table. I want to do this so that one of my users has free access to the data and clan play with it, change it, etc without messing up my data.

The problem I am having is, using VB code, I am trying to write the code to delete all of the records from the access table, and then load all of the records from the Oracle table into the Access table. I keep getting the following error:

Run-time error: '3151'
ODBC -- connection to database failed
[code][/b]
The code that I am trying to run is:
[b][code]
Set dbs2 = wsp.OpenDatabase("CFONE", , , "ODBC;DATABASE=TCP1;UID=mylogin;PWD=mypass;DSN=CFONE")
    
strSQL = "SELECT * INTO AgentList FROM eisadmin_tbl_agent_list"

DoCmd.RunSQL (strSQL)

It is important for me to get this running automatically, without me being here to enter the password.

Any suggestions would be greatly appreciated.

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[huzza]

Hi Terry,
my first thought here is that if you've just deleted the contents of the AgentList table (and not the table itself) then you would actually want an Insert into SQL and not a select into one. The select into statement is for putting the result into a new table.
You would want to change the SQL to:

strSQL = "insert into AgentList * SELECT * FROM eisadmin_tbl_agent_list"

I'm not entirely certain if that first * is required, so if it doesn't work first time try taking it out (I ususally work with a list of fields).
If there is still problems, or you do delete the table before doing this then let me know and I'll see if I can work anything else out,
Richard

 

[THoey]

Thanks Huzza. Yes, actually, I am dropping the table and then was doing a Make Table Query that should recreate the structure and load the data. I have most of that working and am OK when it comes to the SQL, it is just the automatically connecting to the Oracle table from Access that is giving me fits.

Thanks...

Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[huzza]

Terry,
I've just remebered a reply to another port on this forum that I downloaded as I thought it may be of use sometime. Here's what it said
Check the errors collection of the project's connection. 'ODBC - Call Failed' is a sort of default error message. Usually

CurrentProject.Connection.errors(1).description

will have a more descriptive message.
Durkin
alandurkin@bigpond.com
As you're currently using '97 it won't recognise currentproject, but I think you can replace it with currentdb. It may help you find exactly what it's complaining about.
Richard

 

[THoey]

Thanks again Huzza, but that doesn't compile. I even tried backing up the string to look for options, but it loses it at CurrentDB.Connection.

I'll keep looking. I know this is possible...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[huzza]

Hi Terry,
sorry about that last post as I said I'd not checked it out by then. I'm wondering if the example under the "errors collection" part of access help will be of any use as it shows looping through the errors to show them all.
The other thing I've just thought of is where exactly is it the code complains? If you step through does it crash on the runsql or is it on the set dbs2 line?
Richard

 

[Durkin]

I think there's something wrong with your connection string because, for whatever reason, it's not connecting to the database. You should consider linking the table permanently then you just need to set up the connection once and never worry about it again. If you don't want to have a permanent link then it's worth linking it initially and having a look at the connect property of the table in the tabledefs collection which might give you a clue as to what's wrong with your connection string. Also check your DSN to see if it is using the correct login info etc. Durkin
alandurkin@bigpond.com

 

[THoey]

Huzza: The code errors at the DoCmd.RunSQL

Durkin: It may be the connection string, but I have been trying different things to it with no luck. I have gone in and linked the table by using the File -> Get External Data -> Link Table, but this code still gets the prompt for the login and password and I need to be able to run this during the night when I am not here.

Any other suggestions would be greatly appreciated...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?