VBA to move Excel table into Access from excel.

[Richard U]

Situation:

• Due to licensing issues, the user will not have the use of MS Access
• A Link from Access to Excel is not possible due to company policy
• Due to these issues, all code must reside in an Excel spreadsheet

Is there a way to have Excel VBA connect to a database then update a table with the contents of a table/sheet residing in Excel?

I know how to write the connection string...

dim StrSql as string
dim StrConnection  as string
Dim obj_Connection As Object

StrConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDrive:\Mypath\Reports\MyDatabase.accdb"
strSQL = (SQL CODE) +  ????


    obj_Connection.Open StrConnection  

    .......

So, what I need is either:

1. How to design and execute the rest of the query
2. VBA code/commands to set up a record set to send to the database.

It has been about 10 years since I've done any serious interoperability coding, so I'm rusty. I know what to do, I just need some help on how to do it.

 

[alansidman]

Have you considered using MS Access RunTime. It is free and you do not need to have a license.

Check out this link. http://www.hitechcoach.com/index.php

https://www.microsoft.com/en-us/download/details.aspx?id=50040