SELECT Query From Code

[rookery]

Is the only way to run a SELECT query from code to use the OpenRecordset method? I notice that the RunSQL method only works with Action queries and the OpenQuery method displays the results of an already existing query.

I want to keep the record in memory (as with recordsets) and will not be edited.

Therfore am I also right in saying that if I already have a recordset open I will simply have to open another one using a different name?

 

[borg2002]

Hello,
the following code is what I'm using...
Hope it can help you.

Kind regards
Borg

Set conn = New ADODB.Connection 'Define new ADO connection
Set rstmat = New ADODB.Recordse
strc = "dsn=DSNNAME;uid=" & Form_frmLogin.txtUserName & ";pwd=" & Form_frmLogin.txtPassword 'Build Connectionstring
With conn
.CursorLocation = adUseClient 'Define connection as being client side
.ConnectionString = strc 'Use parameters provided in strc String to make a DSN connection
.Open 'Open connection
End With
With rstmat
.CursorLocation = adUseServer 'Define recordset as being server side
.CursorType = adOpenKeyset 'Define cursortype
.LockType = adLockOptimistic 'Define locktype allowing updates
.CacheSize = 50
.Source = "Select FILED1, FIELD2 from TABLENAME
.ActiveConnection = conn 'Define connection to be used for the recordset
.Open 'Open recordset
End With

 

[rookery]

Thanks Borg but I haven't moved into the C21st yet and am using DAO. Any ideas how to do it in this format?

 

[borg2002]

Rookery,

If you include the microsoft activex data objects 2.6 reference. You should be able to use ADO. I don't have an example of DAO, but you should be able to find one in Access' helpfile or on the msdn site.
Following is an extract from the msdn library :

Versions of DAO code prior to 3.6 are no longer supported in Access 2000. It is suggested that you use the ADO methods of accessing data in your current database for all new applications and future version compatibility.

Kind regards
Borg

 

[borg2002]

Rookery,

Following link may get you started :

http://support.microsoft.com/default.aspx?scid=KB;en-us;q164582

Borg

 

[rookery]

Borg I'm aware that I CAN use ADO if I want it's just that I dont know how to write it etc. The rest of my code is in DAO and I doubt I can just mix 'n match the two technologies in the same module.

However all I've done in the meantime is to open another Recordset which seems to be working OK so far...

Thanks again.