VBA - Upload data from Excel to Access without Access installed

Question

I am fairly new to VBA but am trying to upload data from an Excel Workbook to an Access database table from a computer that does not have Access installed. I have searched for a solution online but haven't found anything yet that I can get to work with my code.

The error code I am getting is...429 cannot create activex component

I have some VBA code set up in the Excel workbook which calls a Sub in Access [which works on a machine which has Access installed] but I don't know what the correct code should be if the machine doesn't have Access installed.

Sub Upload_SiteObsData_Excel_To_Access(Database_Path)

Database_Path = "\\Path\db1.mdb"

Dim acApp As Object
Dim db As Object

Set acApp = CreateObject("Access.Application")

acApp.OpenCurrentDatabase ("\\Path\db1.mdb")

Set db = acApp
acApp.Run "Upload_SiteObsData_to_Access"
acApp.Quit
Set acApp = Nothing

End Sub

The procedure in Access is as follows:

Option Compare Database
Option Explicit

Dim Excel_Path As String
Dim Excel_Range As String
Dim UserNameOffice As String

Dim Excel_File_TechForm As String
Sub SetUp_Variables()

UserNameOffice = CreateObject("wscript.network").UserName

Excel_Path = "C:\Documents and Settings\" & UserNameOffice & "\Desktop\"
Excel_Range = "MyData"

Excel_File_TechForm = "SiteObsForm_v0.2.xls"

End Sub

Sub Upload_SiteObsData_to_Access()

SetUp_Variables
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TBL_SiteObsData",   Excel_Path & Excel_File_TechForm, True

End Sub

I would be extremely grateful for any help. Thanks in advance

you dont have to open access to access the db. you can run any other compatible tool to open the db. Have a read on ADODB and different sql strings — user2140173

Gord Thompson Gord Thompson · Accepted Answer · 2013-05-01T12:47:41

I was just fooling around with some Excel VBA code and the following seemed to work:

Option Explicit

Sub Upload_Excel_to_Access()
Dim con As Object  '' ADODB.Connection
Set con = CreateObject("ADODB.Connection")  '' New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data source=C:\Users\Public\Database1.accdb;"
con.Execute _
        "INSERT INTO TBL_SiteObsData " & _
        "SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Users\Public\Book1.xlsm].[Sheet1$]"
con.Close
Set con = Nothing
End Sub

VBA - Upload data from Excel to Access without Access installed

3 Answers