ADO and Oracle

[beruken]

I have a connection to an Oracle DB and it works fine. Once I try to create a recordset though, I get the following error.

"Ora-00942 Table or view does not exist"

I have an Access 2000 odbc linked connection to the same oracle DB and I know what the table names and field names are so that isn't the problem.

sConn = "Provider=MSDASQL.1;DRIVER={Oracle73 Ver 2.5};UID=noOne;PWD=changeme;DBQ=LSUNITS.WORLD;ASY=OFF;"
Set cn = New ADODB.Connection
cn.CursorLocation = adUseServer
cn.ConnectionString = sConn
cn.Open

Set rs = New ADODB.Recordset
sSQL = "SELECT * FROM LSMGR_ACCT"
rs.Open sSQL, cn

 

[CasperTFG]

1) Do you have the Oracle Client software, you must have this to connect to Oracle. To see if you have it from a command prompt enter:
[tt]tnsping Database Name.Server or Host Name[/].World[/tt]

2) Your connection string is very wrong. It should look more like:
[tt]Provider=OraOLEDB.Oracle;Data Source=Database Name.Server or Host Name[/].World;User ID=?;Password=?;[/tt]

3) Unless of course you want to use ODBC. Though what's the point of using an ODBC connection if your already using ADO. I'd stick with #2 for an OLEDB connection but that's up to you.
[tt]Provider=MSDASQL;DSN=The ODBC Entry you created in Data Source Config window (Control Panel);UID=?;PWD=?;[/tt]

I hope this is at least a good start for you... Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[beruken]

Pinging worked fine but your #2 connection path resulted in "error 3706 provider cannot be found". The ODBC connection works fine.

My original connection to the DB was a File DSN that worked great.

sConn = "FileDSN=ls.dsn;" & _
"Server=LsUnits.world;" & _
"Uid=NoOne;" & _
"Pwd=changeme;" & _
"Database=lsunits;"


The issue is grabbing a record. It keeps telling me no such Table exists. Which there is.

 

[CasperTFG]

try calling the table name with the database.
[lsunits].[table1] Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[beruken]

Thanks but that still would not work. I guess i'll have to find a different way to get at the data.

 

[CasperTFG]

Sorry I thought the problem was with your connection. I noticed your SQL was in-complete

sSQL = "SELECT * FROM LSMGR_ACCT";

Though I think it might work without the ;. Do you know exactly where in the code it dies. is it on the Open statement.


Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!