Avatar of GPSPOW
GPSPOWFlag for United States of America asked on

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
Microsoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Norie

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
GPSPOW

Dim MyDb As DAO.Database
Dim qdef As DAO.QueryDef

These two statements are giving me the error:

User-defined type not defined
ASKER
GPSPOW

This worked perfectly after I loaded the DAO library.

Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61