Access automation from Excel

  • Thread starter Thread starter AccessChallenged
  • Start date Start date
A

AccessChallenged

Hello. I need to do some automation of Access from Excel.
Can someone please point me to some good MS sites that
illustrate how to do it, its object models, etc.

For starters, could someone show me a code sample that
gets a reference to a table in an Access DB?

Thanks in advance for your example code/site refs.
 
http://support.microsoft.com/?id=260410
OFF2000: Microsoft Office 2000 Automation Help File Available


http://support.microsoft.com/?id=167223
Microsoft Office 97 Automation Help File Available on MSL

http://support.microsoft.com/?id=177760
VBA: How to Run Macros in Other Office Programs


http://support.microsoft.com/?id=210111
ACC2000: Using Microsoft Access as an Automation Server

http://support.microsoft.com/?id=253338
INFO: Office Developer Samples and Tools Available for Download


http://support.microsoft.com/support/OfficeDev/FaqVBOffice.asp
Frequently Asked Questions about Microsoft Office Automation Using Visual
Basic


http://support.microsoft.com/support/OfficeDev/offdevinapps.asp
Programming Office from Within Office
 
I would suggest using an ADO recordset with Excel 2000 or
above.
Note: You have to make a reference to the "Microsoft
ActiveX Data Objects 2.x Library" in the Visual Basic
Editor>Tools>References dialog to use this code.

Function OpenDataTable()
On Error GoTo HandleError

Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection

Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\AccessDatabaseFile.mdb;"

rst.Open "YourTableOrQuery", cnn, adOpenKeyset,
adLockOptimistic

......Additional Code.......

ExitHere:
Exit Function
HandleError:
MsgBox Err.Description
Resume ExitHere
End Function
 
Back
Top