Switch from Excel to Access

[Djblois]

I am currently using an excel file as a database for my add-in. What it does at different points in the add-in, it looks up information in the excel file and fills it into another Excel file. I want to change the database from an Excel file to an Access file but I have a few questions about it.

1) Does it run quicker to get information from an Access file as opposed to an Excel file?

2) Do I need to open up the access file to get information from it? or can I access the information with the file closed? I know you can get the information from an Excel file when it is closed but it runs much slower.

Thank you,
Daniel

[Bob Phillips]

Using ADO it will be quick(ish) from Excel or an mdb file.

[Djblois]

ok thank you but do I need to open the Access database before extracting data from it?

[Bob Phillips]

Not with ADO you don't.

[vba]

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub
[/vba]

It gets it from the disk file.

[Dr.K]

I've been building a bunch of Apps lately that use an Excel front-end and a Access DB behind it... Its a powerful combination!

The only problem with ADO is that if you are going to be rolling the finished app out to a large user base, its generally safer to use Late Binding. Xld's code above uses Late Binding.

If you are new to ADO, here are a few simple examples of using ADO to fill a combo-box from an Access table:

http://www.fontstuff.com/vba/vbatut10.htm



Edit: Doh, I misunderstood your question.

To clarify what Xld said: NO, you do not have to open Access or the MDB file, the ADO driver can access the DB inside the MDB file automatically, as long as you have file system access to the MDB file. (this means the MDB file can be on a shared drive, folder, server, etc etc)

This means that the end users DO NOT need to install MS Access. If your app uses an Excel front end, the Access DB is completely invisible to the end user. ADO handles all of the connections to the "closed" file.