can I code an ADO connection

[bookouri]

Can I link to an oracle table in Access using ADO code rather than creating an ODBC connection? I can create an OCBC connection, go into my Access database and use the ODBC connection to link to my oracle table, but I have multiple users on my Access database and do not want to maintain OCBC connections on all the workstations if I can avoid it.

thanks

 

[FancyPrairie]

Haven't worked with Oracle yet, but one way around having to set up a DSN on every machine is to define a "File DSN". Then make your connections via the File DSN rather than a User DSN or System DSN. The File DSN should reside on a server that everyone has access to.

When you define your ODBC connection, you are given the option of creating a User, System, or File DSN. My guess is that you are either creating a User or System DSN.

 

[cmmrfrds]

You can set up an ADO connection to the Oracle database and interface with the Oracle database. This would be done in vba code so you won't see the tables you are connecting to under the table interface in Access. Also, you cannot use the ADO recordset as the datasource for Forms in Access 97. Access 2000 has a Form recordset object so an ADO recordset can be the data source for a Form. You would need to open an ADO recordset and use this as the Form's recordset.

To help you in setting up the Oracle connection string use the UDL Wizard.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the Oracle provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

If you need an example of connecting, post back.

 

[bookouri]

thanks for the replies. Im working in Access 97...Im using queries based on the table as the datasource for my forms so Ill have to experiment with the file dsn I guess. But that trick of creating the datasource using a notepad document is too good not to keep.