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
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.


Microsoft ExcelMicrosoft Access

Avatar of undefined
Last Comment
Avatar of Norie

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of GPSPOW
Flag of United States of America image


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

These two statements are giving me the error:

User-defined type not defined
Avatar of GPSPOW
Flag of United States of America image


This worked perfectly after I loaded the DAO library.

Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo