ODBCDirect

[Nicholas]

I am trying to connect to oracle database and i've tried using Microsoft Jet
workspace and ODBCDirect workspace. Once i run the macro at microsoft office
2007 under windows vista and i get the error "Run time error '3847 ODBCDirect
is no longer supproted. Rewrite the code to use ADO instead of DAO'".

My coding is as following.

Function PopulateListOfOdbcTableToLink(OdbcName As String, ByRef
ListOfTables As Collection) As Integer
Dim wrkJet As Workspace
Dim dbs As Connection
Dim tdf As TableDef
Dim Atable As Variant
Dim Rs As Recordset
Dim AllTablesSql As String
Dim rc As Variant
'
On Error GoTo PopulateListOfOdbcTableToLink
'
' Open Microsoft Jet Database object.
Set wrkJet = CreateWorkspace("MyWorkspace", _
"", "", dbUseODBC)
'
' Initialise the progress bar
'
rc = SysCmd(acSysCmdSetStatus, "Finding the Tables to link ... ")
DoEvents

Set dbs = wrkJet.OpenConnection(OdbcName)
'
' Populate the collection
'

AllTablesSql = "select OBJECT_NAME from all_objects " & _
"where owner like 'IDB%' and object_type in ('TABLE',
'VIEW')"
'
Set Rs = dbs.OpenRecordset(AllTablesSql)
While Not Rs.EOF
ListOfTables.Add Rs.Fields(0).Value
Rs.MoveNext
Wend
dbs.Close
'
' Clean the status bar
'
rc = SysCmd(acSysCmdClearStatus)
PopulateListOfOdbcTableToLink = 0
Exit Function
PopulateListOfOdbcTableToLink:
MsgBox "An error has occured : Wrong Odbc name given or invalid Oracle
user/password. You will not be able to configure the Idb until the Linking is
performed.", vbCritical
PopulateListOfOdbcTableToLink = 1
End Function


Can anyone point out what wrong with my coding and how to amend it? Many
Thanks.

 

[Albert D. Kallal]

I am trying to connect to oracle database and i've tried using Microsoft
Jet
workspace and ODBCDirect workspace. Once i run the macro at microsoft
office
2007 under windows vista and i get the error "Run time error '3847
ODBCDirect
is no longer supproted. Rewrite the code to use ADO instead of DAO'".

That message is 100% correct. ODBCDirect workspaces are not supported
anymore...

You have to re-write your code as ADO.

Another really simple approach is to use a pass-through query.

So, fire up the query builder. Create a new query, (don't add any tables).
Go sql view, then paste in that sql you have now...

, then go query->sql specific->pass through

Test the query...if it works, then your code is *much* more simple:

Set Rs = currentdb.querydefsdbs.("name of that query").Execute

The rest of your code should work un-modified:

 

[Sascha Trowitzsch]

That message is 100% correct. ODBCDirect workspaces are not supported
anymore...
You have to re-write your code as ADO.
Another really simple approach is to use a pass-through query.

But one should be aware that pass-through queries are not updatable.
ADO-ODBC recordsets are usually.

Ciao, Sascha

 

[Nicholas]

i found that my sql query in sql view does not run. My sql is as below....

AllTablesSql = "select OBJECT_NAME from all_objects " & _
"where owner like 'IDB%' and object_type in ('TABLE',
'VIEW')"

Is it has any problem with my sql?

 

[Albert D. Kallal]

i found that my sql query in sql view does not run. My sql is as below....

AllTablesSql = "select OBJECT_NAME from all_objects " & _
"where owner like 'IDB%' and object_type in ('TABLE',
'VIEW')"

Is it has any problem with my sql?

You need to make the query pass-though.....