CODEWITHSUNDEEP
excelms-accessvba
David G
David G

Reputation: 2355

Importing data from Access Table in runtime

I import data from an Access database just fine. However, I want to make the DB a runtime (.accdr instead of .accde). The Runtime works fine, too, on its own, but I cannot import data from a runtime application when in Excel (it does not permit it).

How can I contact my database and have a Runtime mode?

Nothing on the web I have found even mentions this problem. I have no problem with opening an ADODB connection with VBA if it is required.

Upvotes: 1

Views: 434

Answers (1)

HansUp
HansUp

Reputation: 97131

Since ACCDR databases are not displayed among the choices offered by Excel's Data>From Access option, you can open a recordset to retrieve your Access data and then use Excel's CopyFromRecordset Method to save those data into a worksheet.

This code uses a DAO recordset loaded from an ACCDR database file and saves the data in the active worksheet ...

Const cstrDbPath As String = "C:\share\Access\Database2.accdr"
Const cstrDao = "DAO.DBEngine.120"
Dim dbe As Object ' DAO.DBEngine
Dim db As Object ' DAO.Database
Dim rs As Object ' DAO.Recordset
Dim strSql As String
strSql = "SELECT 'Hello World' AS greet_world;"
Set dbe = CreateObject(cstrDao)
Set db = dbe.OpenDatabase(cstrDbPath, True)
Set rs = db.OpenRecordset(strSql)
Range("A2").CopyFromRecordset rs
rs.Close
db.Close

That code uses DAO with late binding. If you prefer early binding, add "Microsoft Office <version> Access database engine Object Library" to your project's references.

Or if you prefer ADO, CopyFromRecordset will also work with an ADO recordset.

Upvotes: 2

Related Questions