Passing variables from Excel Module to Access Module

I have two VBA modules.

the first is in Excel and creates two date variables and then calls an Access Macro (which runs a Function Module)  which creates a table based on the Date Variables.

When I run the Excel module the Access Query runs but the dates do not pass over and the query hangs up.

Here are the two modules.  Can you tell me what I can do to correct this?

Excel Module:

Private Sub RunCreateTable()

Dim strDb As String
Dim appAccess As Object
Dim ModName As String

Dim startdt As String
Dim enddt As String

startdt = "#" & Format$(DateSerial(2013, 4, 1), "mm/dd/yyyy") & "#"
enddt = "#" & Format$(DateSerial(2013, 7, 31), "mm/dd/yyyy") & "#"


ModName = "mcrCreate"

strDb = "P:\Accounting\NHSN.accdb"

Set appAccess = CreateObject("Access.Application")

With appAccess
    .OpenCurrentDatabase strDb
    .DoCmd.RunMacro ModName
    .Quit
End With

Set appAccess = Nothing




End Sub



Access Module:

Option Compare Database

Function CreateNHSNTbl()

DoCmd.SetWarnings False



Dim MyDb As DAO.Database
Dim qdef As DAO.QueryDef
Dim SQL As String
Dim strWhere As String

Set MyDb = CurrentDb()

MyDb.QueryDefs.Delete "qry_NHSN_tbl"



strWhere = " WHERE(((dbo_AbstractData.PatientClass) <> 'IN')) AND (dbo_SchAppointmentOrOperations.Description IS NOT NULL) "
strWhere = strWhere & "GROUP BY dbo_SchOrPatCases.SurgeonName, dbo_AbstractData.Name, dbo_AbstractData.BirthDateTime, dbo_AbstractData.UnitNumber, "
strWhere = strWhere & "dbo_SchAppointments.DateTime, dbo_SchAppointmentOrOperations.Description, dbo_SchOrPatCases.VisitID, dbo_AbstractData.AccountNumber "
strWhere = strWhere & "HAVING (dbo_SchAppointments.DateTime)between " & startdt & " and " & enddt & ";"

SQL = "SELECT dbo_SchOrPatCases.SurgeonName AS Surgeon, dbo_AbstractData.Name AS PTName, dbo_AbstractData.BirthDateTime AS DOB, "
SQL = SQL & "dbo_AbstractData.UnitNumber AS [MedRec#], dbo_SchAppointments.DateTime AS ProcDate, dbo_SchAppointmentOrOperations.Description AS ProcDescr "
SQL = SQL & " into tblNHSNProcedures "
SQL = SQL & " FROM (((((dbo_SchOrPatCases LEFT JOIN dbo_AbsOperationProcedures ON dbo_SchOrPatCases.VisitID = dbo_AbsOperationProcedures.VisitID) "
SQL = SQL & "LEFT JOIN dbo_AbstractData ON dbo_SchOrPatCases.VisitID = dbo_AbstractData.VisitID) LEFT JOIN dbo_AdmVisitOrders "
SQL = SQL & "ON dbo_SchOrPatCases.VisitID = dbo_AdmVisitOrders.VisitID) LEFT JOIN Sheet1 ON dbo_AbsOperationProcedures.ProcedureCode = Sheet1.ProcedureCode) "
SQL = SQL & "LEFT JOIN dbo_SchAppointments ON dbo_AbstractData.VisitID = dbo_SchAppointments.VisitID) LEFT JOIN dbo_SchAppointmentOrOperations "
SQL = SQL & "ON dbo_SchAppointments.AppointmentID = dbo_SchAppointmentOrOperations.AppointmentID "
SQL = SQL & strWhere


Set qdef = MyDb.CreateQueryDef("qry_NHSN_tbl", SQL)

DoCmd.OpenQuery "qry_NHSN_tbl"



End Function


I tested the Access Module with the date variables within the Access Module and it works fine.  The end product is to reference cells in Excel for the dates.

thanks

glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Glen

You aren't passing any arguments to the Access function.

Actually I'm not even sure you are calling the function.

This looks more like you are calling the module the function is in.
.DoCmd.RunMacro ModName

Open in new window


Have you tried combining both sets of code in Excel?

For example:
Option Explicit

Private Sub RunCreateTable()
Dim strDb As String
Dim appAccess As Access.Application
Dim ModName As String

Dim startdt As String
Dim enddt As String
Dim MyDb As DAO.Database
Dim qdef As DAO.QueryDef
Dim SQL As String
Dim strWhere As String

    startdt = "#" & Format$(DateSerial(2013, 4, 1), "mm/dd/yyyy") & "#"
    enddt = "#" & Format$(DateSerial(2013, 7, 31), "mm/dd/yyyy") & "#"

    strDb = "P:\Accounting\NHSN.accdb"

    Set appAccess = CreateObject("Access.Application")

    With appAccess
    
        .OpenCurrentDatabase strDb
        
        .DoCmd.SetWarnings False

        Set MyDb = .CurrentDb()

        MyDb.QueryDefs.Delete "qry_NHSN_tbl"

        strWhere = " WHERE(((dbo_AbstractData.PatientClass) <> 'IN')) AND (dbo_SchAppointmentOrOperations.Description IS NOT NULL) "
        strWhere = strWhere & "GROUP BY dbo_SchOrPatCases.SurgeonName, dbo_AbstractData.Name, dbo_AbstractData.BirthDateTime, dbo_AbstractData.UnitNumber, "
        strWhere = strWhere & "dbo_SchAppointments.DateTime, dbo_SchAppointmentOrOperations.Description, dbo_SchOrPatCases.VisitID, dbo_AbstractData.AccountNumber "
        strWhere = strWhere & "HAVING (dbo_SchAppointments.DateTime)between " & startdt & " and " & enddt & ";"

        SQL = "SELECT dbo_SchOrPatCases.SurgeonName AS Surgeon, dbo_AbstractData.Name AS PTName, dbo_AbstractData.BirthDateTime AS DOB, "
        SQL = SQL & "dbo_AbstractData.UnitNumber AS [MedRec#], dbo_SchAppointments.DateTime AS ProcDate, dbo_SchAppointmentOrOperations.Description AS ProcDescr "
        SQL = SQL & " into tblNHSNProcedures "
        SQL = SQL & " FROM (((((dbo_SchOrPatCases LEFT JOIN dbo_AbsOperationProcedures ON dbo_SchOrPatCases.VisitID = dbo_AbsOperationProcedures.VisitID) "
        SQL = SQL & "LEFT JOIN dbo_AbstractData ON dbo_SchOrPatCases.VisitID = dbo_AbstractData.VisitID) LEFT JOIN dbo_AdmVisitOrders "
        SQL = SQL & "ON dbo_SchOrPatCases.VisitID = dbo_AdmVisitOrders.VisitID) LEFT JOIN Sheet1 ON dbo_AbsOperationProcedures.ProcedureCode = Sheet1.ProcedureCode) "
        SQL = SQL & "LEFT JOIN dbo_SchAppointments ON dbo_AbstractData.VisitID = dbo_SchAppointments.VisitID) LEFT JOIN dbo_SchAppointmentOrOperations "
        SQL = SQL & "ON dbo_SchAppointments.AppointmentID = dbo_SchAppointmentOrOperations.AppointmentID "
        SQL = SQL & strWhere

        Set qdef = MyDb.CreateQueryDef("qry_NHSN_tbl", SQL)

        .DoCmd.OpenQuery "qry_NHSN_tbl"
        
        .DoCmd.SetWarnings = True
        .Quit
    End With

    Set appAccess = Nothing

End Sub

Open in new window


NOTE: For this code to run you'll need these references:

  Microsoft Access x.0 Object Library (x.0 will be different for different versions of Office, eg for Office 2010 it's 14.0)
 
  Microsoft DAO 3.6 Object Library
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
Dim MyDb As DAO.Database
Dim qdef As DAO.QueryDef

These two statements are giving me the error:

User-defined type not defined
0
GPSPOWAuthor Commented:
This worked perfectly after I loaded the DAO library.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.