I originally posted this thread in the Access forum, but haven't received the correct answer yet. Anyone out there have an Access front end attached to an Oracle DB that might know?
*********************************************************
I have an MS Access database (97, but going to be moved to 2K) and an Oracle 7.3.4 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
The code that I am trying to run is:
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?
*********************************************************
I have an MS Access database (97, but going to be moved to 2K) and an Oracle 7.3.4 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
The code that I am trying to run is:
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?
