[RESOLVED] VBA and Executing SQL Statements

[Besoup]

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?

[DKenny]

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

[Besoup]

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

[DKenny]

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

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.

[RobDog888]

You can use this to execute a sql string.

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.

[RobDog888]

And this one to open a recordset

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.

[Besoup]

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

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

[DKenny]

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

[Besoup]

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

[Besoup]

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!

[DKenny]

Try the following...

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