taporctv
08-15-2007, 06:22 AM
Public Sub runQueryFromDB(query As String, Optional ByRef myRS As DAO.Recordset)
' Add the reference to Microsoft Access 11.0 Lib.
' Add the DAO 3.6 reference
Dim myAccess As access.Application
Dim myDB As Database
Set myAccess = CreateObject("Access.Application")
' Relative path
Dim CurDir As String
CurDir = ThisWorkbook.Path & "\IA Testing.mdb"
' myAccess.Application.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase (CurDir)
' open access query normally
myAccess.DoCmd.OpenQuery (query)
Application.DisplayAlerts = False
If myRS Is Nothing Then
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
End If
' myAccess.DoCmd.Close
myAccess.Quit
Set myAccess = Nothing
End Sub
How can I check if myRS has been passed or not? I tried IsMissing but thats only for varaints. The reason im asking is because at times no recordset is needed when a query is excuted. So when I pass a query and no recordset to this function, im getting an error because the function thinks there is a recordset to populate.
' Add the reference to Microsoft Access 11.0 Lib.
' Add the DAO 3.6 reference
Dim myAccess As access.Application
Dim myDB As Database
Set myAccess = CreateObject("Access.Application")
' Relative path
Dim CurDir As String
CurDir = ThisWorkbook.Path & "\IA Testing.mdb"
' myAccess.Application.Visible = False 'if you don't run this the database will run in the background hidden
myAccess.OpenCurrentDatabase (CurDir)
' open access query normally
myAccess.DoCmd.OpenQuery (query)
Application.DisplayAlerts = False
If myRS Is Nothing Then
' create recordset
Set myDB = myAccess.CurrentDb()
Set myRS = myDB.OpenRecordset(query)
End If
' myAccess.DoCmd.Close
myAccess.Quit
Set myAccess = Nothing
End Sub
How can I check if myRS has been passed or not? I tried IsMissing but thats only for varaints. The reason im asking is because at times no recordset is needed when a query is excuted. So when I pass a query and no recordset to this function, im getting an error because the function thinks there is a recordset to populate.