Hi all.
I have soem code that works fine on SQL Server, but something isnt right for Oracle.
Using DAO in VB, I use ODBC to connect to a DSN. I can use the DSN happily from Access or MS Query.
If I use OpenRecordSet, it works, but only if I specify a table name, NOT if I use 'Select x from y' statements.
Also, the tablename thing only works if I supply the owner as a prefix.
eg OpenRecordset("tablecreator.person") instead of OpenRecordSet("person")
This despite the fact I use the tablecreator UID in the connect string.
db.Execute statements to update tables all fail.
The code I am using (simplified for example purpose) is shown here. Anyone any idea what I am doing so wrong for use in Oracle?
dim dbeng
dim rs
szConnectionString = "odbc;dsn=mydsn;uid=tablecreator;pwd=prettyplease;"
'Set the dbEng object using OLE
Set dbEng = CreateObject("DAO.DBEngine.36")
'assume that works
'Open a database.
Set Db = dbEng.Workspaces(0).OpenDatabase("", False, False, szConnectionString)
'assume that works too
'then, this works
set rs = db.openrecordset("tablecreator.person")
'but this does not
set rs = db.openrecordset("person") '(table not found error)
'nor does this
db.execute "update person set name = 'Jeremy'"
'nor this
set rs = db.openrecordset("Select name from tablecreator.person")
Enter your message below
Sign in or Join us (it's free).