troubleshooting Question

Passing variables from Excel Module to Access Module

Avatar of GPSPOW
GPSPOWFlag for United States of America asked on
Microsoft AccessMicrosoft Excel
3 Comments1 Solution436 ViewsLast Modified:
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.


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros