Simple Access code queries [resolved]

Hi,

I need help with some of the most basic database coding in the world, (haven't done any for 4 years....ish). I am trying to write some VBA in MS Access, and have two main, (initial), questions.

1. How do you run an SQL SELECT statement from VBA Access? I can run a simple update, but my select goes pair shaped...see code below:
2. How do I get the results out of the returned object\recordset?

Any and all help will be very much appreciated.

Cheers

Simple Update that works fine

VB Code:

Dim conDatabase As ADODB.Connection
Dim strSQL As String
 
    Set conDatabase = CurrentProject.Connection
 
    strSQL = "UPDATE SIMs SET Status = 'Active'"
    conDatabase.Execute strSQL
 
    MsgBox "All status' have been set to ""Active""."
 
    conDatabase.Close
    Set conDatabase = Nothing

But this SELECT statement returns -1 in the recordset size

VB Code:

Dim conDatabase As ADODB.Connection
Dim strSQL As String
Dim rst As Recordset
 
    Set conDatabase = CurrentProject.Connection
 
    strSQL = "SELECT * FROM SIMs"
    Set rst = conDatabase.Execute(strSQL)
    For i = 1 To i = rst.RecordCount
        result = rst.GetString
        MsgBox (result)
    Next i
    
    conDatabase.Close
    Set conDatabase = Nothing

instead of conDatabase.Execute try conDatabase.OpenRecordset (you may need to set some extra parameters).

As suggested, I am trying out the OpenRecordSet command, but I am having a few problems. I have altered the

VB Code:

Set rst = conDatabase.Execute(strSQL)

to

VB Code:

Set rst = conDatabase.OpenRecordSet("SELECT * FROM SIMs")

and I am now getting an error message stating that my parameters are either too long, conflict with each other or are of the wrong type. I have checked the MSDN website and it says that it should be used thus:

Set recordset = object.OpenRecordset(source, type, options, lockedits)

where

source
A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects in Microsoft Jet databases, the source can only be a table name.

and the other two parameters are optional. Can anyone see what I am doing wrong?

I have also tried out an MSDN example, (altering the SQL), but Access doesn't recognise the "Database" in the first DIM statement......any ideas on either this one or the above issue?

VB Code:

Sub selectAll()
    Dim dbs As Database, rst As Recordset
    Dim strSQL As String
 
    ' Return reference to current database.
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM SIMs"
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveLast
    Debug.Print rst.RecordCount
    rst.Close
    Set dbs = Nothing
End Sub

Hello;

check out this current thread:

http://www.vbforums.com/showthread.p...hreadid=286325

There is code there for ADO that uses a command object. I have never had a problem when executing my queries this way. I also use it to do updates and inserts. With regards to the record count property, ADO does not support server side record counts (atleast 2.5, the one I am working with)....so you need to do:

VB Code:

conn.CursorLocation = adUseClient 'conn being a connection object

ADO can do a lot - once you get going you'll see its power!!

EXCELLENT!!!! It all works perfectly! Thanks for the help, it was very much needed and is very much appreciated

The working solution.......basically copied from the link above

VB Code:

Sub selectAll()
Dim conDatabase As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
Dim valueString As String
 
    valueString = ""
    Set conDatabase = CurrentProject.Connection   'IS THIS CORRECT
    Set cmdCommand.ActiveConnection = conDatabase 'IS THIS CORRECT?
    cmdCommand.CommandType = adCmdText
 
    sql = "SELECT * FROM SIMs"
    cmdCommand.CommandText = sql
 
    Set rs = cmdCommand.Execute
 
    Do While Not rs.EOF
        valueString = ""
        
        valueString = rs.Fields("Network_Code").Value
        valueString = valueString + ", " + rs.Fields("SIM_No").Value
        MsgBox (valueString)
        rs.MoveNext
    Loop
 
End Sub

VB Code:

Sub selectAll()
Dim conDatabase As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
Dim sql As String
Dim valueString As String
 
    valueString = ""
    Set conDatabase = CurrentProject.Connection   'IS THIS CORRECT <-----NO - it should be ADODB.Connection
 
' you also need to pass a connection string to the connection object and open it first before this code will work
    Set cmdCommand.ActiveConnection = conDatabase 'IS THIS CORRECT?
    cmdCommand.CommandType = adCmdText
 
    sql = "SELECT * FROM SIMs"
    cmdCommand.CommandText = sql
 
    Set rs = cmdCommand.Execute
 
    Do While Not rs.EOF
        valueString = ""
        
        valueString = rs.Fields("Network_Code").Value
        valueString = valueString + ", " + rs.Fields("SIM_No").Value
        MsgBox (valueString)
        rs.MoveNext
    Loop
 
End Sub

check out this link for connection strings and opening connection object:

http://www.able-consulting.com/ADO_Conn.htm

cheers