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

[THoey]

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&quot

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?

 

[moulab3]

Hi,
Have you set up the driver odbc for oracle 7.3.4.
If so:
Have you created the dsn in the control panel odbc32-dsn-new (or add)- choose odbc driver for oracle.- name :CFONE
connect string: CFONE.
If so
Have you tried to import from access 97 or 2k :table -new -import or attach- odbc and you see oracle tables viewss etc .. and you choose your table.
If so:
Have you checked in the tnsnames.ora (path :
\orawin95\network\admin\tnsnames.ora) if you have the same connect string (CFONE.world etc...).

If so:
Try in the dos path and see if c:\orawin95\bin appear in the path.

Bye

lat.am@sympatico.ca

lat.am@netcourrier.com

 

[Rania]

Hii<
i would like to know the time by which i can access two exact databases with equal number of records but one is implemented with oracle and the other in access.
Thank you.

 

[MikeLacey]

Terry, does this script work in the daytime?
Mike
michael.j.lacey@ntlworld.com

 

[THoey]

Mike,

I have had to put this on the back burner. Actually, I forgot I still had this marked for notification until rania brought it up from the bowels of the thread list.

When I get back to this, I will repost if I still have a problem.

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?