Access, direct queries and VBA 2

[ViAn]

I need to perform direct queries from Access to an Oracle Database. Is it possible to manipulate saved queries from the VBA? (Visual Basic for Applications and Access 2000)

In VBA I'll make SELECT-statements (SQL) based on user input in a form. Then I want to perform a "direct query" to an Oracle Database. I can see two possible solutions:
1) From VBA: Alter the SQL-statements of a Access-query included in the project that is set up to be a direct query.
2) From VBA: Make a direct query, including providing all necessary information (username, password, service name of database).

I don't know how to solve any of these tasks...

-vidar

 

[jrbarnett]

Hi Vidar

Yes, it is easily possible to manipulate queries from within VBA and run them. Take a look at the QueryDef object for action queries and Recordsets for Select.

Regards

John

 

[ViAn]

Thank you for answering!
I have read quite a lot about QueryDef, theoretically it seems to be the solution.

There is one problem, though:
I cannot find the "QueryDef" in the objectbrowser, as I cannot find the "Database"-object.

I have searched through "all libraries" and each one separately and the only hit is the constant DB_QUERY_DEF (that equals 5). This is the result in my Norwegian versions of both Office 2000 and Office XP Pro.

Therefore VBA does not give any helping hand when it comes to members, methods and syntax).

Anyone knows where to find it? (Both a helping hand and the objects)

 

[TonyJollans]

Hi Vidar,

In Access 2000, Database is a member of DAO. Under Tools > References, make sure you have "Microsoft DAO 3.6 Object Library" checked.

Enjoy,
Tony

 

[ViAn]

Thank you for helping! It embarrasses me, but I still have problems making a direct query to an Oracle database.

This code seems to work:

Public Sub ConnectionStringX()
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "DSN=MyDSN;UID=MyUID;PWD=MyPWD;"
cnn1.Open
...

1) So, now I have an ADODB.Connection. But can it be used to perform a direct query? How?

I tried the example of the "prepare" property, where a connection is established that is used by a QueryDef. It starts like this:

Sub PrepareX()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim qdfTemp As QueryDef
Dim rstTemp As Recordset

' Create ODBCDirect Workspace object and open Connection
' object.
Set wrkODBC = CreateWorkspace("", _
"admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("Publishers", , , _
"ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers&quot
...

Of course I get an ODBC error, because the specified database does not exist in my computer's world.

When using the cnn1.ConnectionString above, I hoped for a better result, but the error message is:
-Invalid argument.

When keeping the "ODBC; DATABASE=MyDBS" in addition to the ConnectionString, the error message is:
-Type mismatch.

I also tried to use the entire connection string given in a wizard-created access-direct-query (this query works!) with the same result.

2) Do I make an obvious error in the wrkODBC.OpenConnection?

This became quite complex and detailed. Still I would be very glad if anyone is able to help!

Thank you in advance,
Vidar