[RESOLVED] VBA and Executing SQL Statements

Quick question... what do you need for connection info to execute an sql statement from within Access VBA? Can I do it just like using ADO in VB?

Yes, you can use ADO in Access VBA, pretty much exactly as in VB.

what would I use for the connection? Is there a variable to automatically connect to the current DB?

First off. you will need to add a reference to the Microsoft ActiveX Data Objects library.

To create recordsets from the current DB you would use the following syntax

VB Code:

Dim rsMyRecordSet As ADODB.Recordset
Dim sSQL As String
 
    Set rsMyRecordSet = New ADODB.Recordset
    sSQL = "SELECT field FROM table....etc"
    rsMyRecordSet.Open sSQL, CurrentProject.Connection

the "CurrentProject.Connection" in the above connects to the db in which your code resides.

You can use this to execute a sql string.

VB Code:

Public Function ExecuteMe(ByVal sSQL As String)
    Application.CurrentDb.Connection.Execute sSQL
End Function

With a few changes you can make it a Command Object too.

And this one to open a recordset

VB Code:

Public Function OpenMe(ByVal sSQL As String) As Recordset
    Dim rs As Recordset
    Set rs = Application.CurrentDb.OpenRecordset(sSQL, dbOpenDynamic, , dbOptimistic)
    OpenMe = rs
End Function

Sorry for the delay, had a phone call. :)

Ok I have tried all 3 of you guys' suggestions. However I got errors on all of them. The last one I tried was

VB Code:

Dim rs As Recordset
Set rs = Application.CurrentDb.OpenRecordset(sSQL, dbOpenDynamic, , dbOptimistic)
OpenMe = rs

I would like to open it as a recordset but I got an error on the Set statement:

Invalid Arguement

1/ Have you added a reference to the Microsoft ActiveX Data Objects library?

2/ Change
Dim rs as Recordset
to
Dim rs as ADODB.Recordset

Yes I have referenced Microsoft ActiveX Data Objects 2.1 Library and this is the exact code I am using:

Dim rs As ADODB.Recordset
Dim stSQL As String
stSQL = "SELECT * FROM PIU_Percentages"

Set rs = Application.CurrentDb.OpenRecordset(stSQL, dbOpenDynamic, , dbOptimistic)
OpenMe = rs

Got this to work:

Dim rs As ADODB.Recordset
Dim stSQL As String
stSQL = "SELECT * FROM PIU_Percentages"


Set rs = New ADODB.Recordset

rs.Open stSQL, CurrentProject.Connection


Dunno why it didn't work the first time I tried it but thanks for your help guys!

Try the following...

VB Code:

Sub test()
Dim rs As ADODB.Recordset
Dim sSQL As String
 
    Set rs = New ADODB.Recordset
    
    sSQL = "SELECT * FROM PIU_Percentages"""
    
    rs.Open Source:=sSQL, ActiveConnection:=CurrentProject.Connection, _
            CursorType:=adOpenDynamic, LockType:=adLockOptimistic
    
    rs.MoveFirst
    MsgBox rs.Fields(0).Value
    
    rs.Close
    Set rs = Nothing
End Sub