Hi there...I am hoping someone can help with this.
I am currently connecting to an Access DB using an ADO connection.
Using an Excel macros, I scrub data, do validation checks, etc and then push the data to a temp. table in Access.
From there, Access has a macros that will merge the data in the temp table to other tables.
My problem is, to run the Access Macros, I have to open Access and run it manually after the excel macros is done. I would like to call the Access macros from Excel so that the procedure completes in one step without opening Access.
I have some code that sort of does it, but it actually opens access in order to do it. I would like to do it through the same ADO connection without opening Access. Is this possible?
Here is the code that I am using to create the ADO connection and the code to do the update. Any help on this would be great. Thanks.
Cheers.
Public Sub CreateConnection()
Dim con As New ADODB.Connection
Dim sheet As Worksheet
Dim dbname As String
Dim dbUser As String
Dim dbPassword As String
Dim sysDB As String
Dim ctrlSheet As String
Dim StartTime As Date
Application.ScreenUpdating = False
'Start Timer
StartTime = Timer
'Define the variable for ctrlSheet - which is the main sheet
'in the workbook to run the code from
ctrlSheet = "Main Menu"
'Set variables for the database connection
dbname = "path to db"
dbUser = "udername" 'logon name to be used
dbPassword = "password" 'password for log on as above user
sysDB = "path to sys DB" 'path to the workgroups folder where the permissions are stored - this is necessary if security is set on the DB
'This connection string does not need mod's - it uses the variables for connection listed above
'if the user name or password changes - just change it in the variable above
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & dbPassword & ";" & _
"User ID=" & dbUser & ";" & _
"Data Source=" & dbname & ";" & _
"Persist Security Info=True;" & _
"Jet OLEDB:System database=" & sysDB & ";"
'call the macros to create the recordset and pass the connection and ctrlsheet name
Call CreateRecordSet(con, ctrlSheet, rtype, service)
Call Upload_to_Access(con) '
'call the macros in access
Call Call_Access_Macros(con, dbname)
'Close the connection to the DB
con.Close
'Display the elapsed time
MsgBox "The procedure took " & Format(Timer - StartTime, "00.00") & " seconds to create the reports.", _
vbInformation, "Reports Successfully Created"
End Sub
Sub Call_Access_Macros(dbname As String)
Dim appaccess As Object
' Get a reference to the Access Application object.
Set appaccess = CreateObject("Access.Application")
' Open your db.
' Modify the path as needed.
appaccess.OpenCurrentDatabase dbname
' hide the application.
appaccess.Visible = False
appaccess.DoCmd.RunMacro "MyMacros"
' Close the database.
appaccess.CloseCurrentDatabase
' Quit Access.
appaccess.Quit
' Close the object variable.
Set appaccess = Nothing
End Sub
I am currently connecting to an Access DB using an ADO connection.
Using an Excel macros, I scrub data, do validation checks, etc and then push the data to a temp. table in Access.
From there, Access has a macros that will merge the data in the temp table to other tables.
My problem is, to run the Access Macros, I have to open Access and run it manually after the excel macros is done. I would like to call the Access macros from Excel so that the procedure completes in one step without opening Access.
I have some code that sort of does it, but it actually opens access in order to do it. I would like to do it through the same ADO connection without opening Access. Is this possible?
Here is the code that I am using to create the ADO connection and the code to do the update. Any help on this would be great. Thanks.
Cheers.
Public Sub CreateConnection()
Dim con As New ADODB.Connection
Dim sheet As Worksheet
Dim dbname As String
Dim dbUser As String
Dim dbPassword As String
Dim sysDB As String
Dim ctrlSheet As String
Dim StartTime As Date
Application.ScreenUpdating = False
'Start Timer
StartTime = Timer
'Define the variable for ctrlSheet - which is the main sheet
'in the workbook to run the code from
ctrlSheet = "Main Menu"
'Set variables for the database connection
dbname = "path to db"
dbUser = "udername" 'logon name to be used
dbPassword = "password" 'password for log on as above user
sysDB = "path to sys DB" 'path to the workgroups folder where the permissions are stored - this is necessary if security is set on the DB
'This connection string does not need mod's - it uses the variables for connection listed above
'if the user name or password changes - just change it in the variable above
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=" & dbPassword & ";" & _
"User ID=" & dbUser & ";" & _
"Data Source=" & dbname & ";" & _
"Persist Security Info=True;" & _
"Jet OLEDB:System database=" & sysDB & ";"
'call the macros to create the recordset and pass the connection and ctrlsheet name
Call CreateRecordSet(con, ctrlSheet, rtype, service)
Call Upload_to_Access(con) '
'call the macros in access
Call Call_Access_Macros(con, dbname)
'Close the connection to the DB
con.Close
'Display the elapsed time
MsgBox "The procedure took " & Format(Timer - StartTime, "00.00") & " seconds to create the reports.", _
vbInformation, "Reports Successfully Created"
End Sub
Sub Call_Access_Macros(dbname As String)
Dim appaccess As Object
' Get a reference to the Access Application object.
Set appaccess = CreateObject("Access.Application")
' Open your db.
' Modify the path as needed.
appaccess.OpenCurrentDatabase dbname
' hide the application.
appaccess.Visible = False
appaccess.DoCmd.RunMacro "MyMacros"
' Close the database.
appaccess.CloseCurrentDatabase
' Quit Access.
appaccess.Quit
' Close the object variable.
Set appaccess = Nothing
End Sub
