RunSQL - Only for Action Queries?? 1

[JackR]

If DoCmd.RunSQL can only be used for action queries,
how can you perform a simple SELECT (from a SQL table)
to return a table value to an Access function...?

Sorry, this seems too simple to me - I'm missing something here.... ?

Thanks,

Jack

 

[tlbroadbent]

To return a single value use DLookup function.

sVal = DLookup("[col1]","table1","[col2]=1&quot

To Open a table from code use the DoCmd OpenTable method.

DoCmd.OpenTable "table1", acViewPreview

To open an ADO recordset in VB code use the RecordSet Open method.

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim varDate As Variant

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Open employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[JackR]

Hi Terry,

Thank you for your thoughtful response - am working with it now..

Thanks

Jon