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
– user2140173, Commented May 1, 2013 at 10:41

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

1

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

answered May 1, 2013 at 12:47

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2338876 Over a year ago

Hi Gord, Looks good. I notice you are using Office 2010...we are still running Office 2003. Do you know what the syntax should be for 2003 and I can give it a try. Many thanks

Chris · Accepted Answer · 2013-05-01 10:37:33Z

0

I think you'l have to find another way round this issue, without access installed, Excel cannot create the "Access.Application" object, it just flat-out doesn't know what access is.

Can you pull the data from Access instead?

answered May 1, 2013 at 10:37

Chris

2,9381 gold badge33 silver badges45 bronze badges

3 Comments

user2338876 Over a year ago

Hi, It's purpose is to upload form data from several users located in different geographical areas [but connected to the same network] to master database. Installing Access on each computer is an added expense we were trying to avoid.

Chris Over a year ago

Is someone running the access application, or is it just a datastore?

user2338876 Over a year ago

Access is just the datastore

Community · Accepted Answer · 2017-05-23 12:30:12Z

0

I've done this the first time I connected Excel to Access via VBA, and I know for sure it works using the code that Gord Thomson provided. Establishing an ADODB connection and executing an append query.

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Jul 30, 2015 at 11:09

Braz123

111 bronze badge

Collectives™ on Stack Overflow

VBA - Upload data from Excel to Access without Access installed

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related