how to connect excel to an access database using vba?
Scott
query to, and possible make changes to through excel. is
there a way to program in excel with VBA to accomplish
this. if anyone can help i would appreciate it. thanks.
Tom Ogilvy
http://www.erlandsendata.no/english/vba/adodao/index.htm
General data programming articles
http://msdn.microsoft.com/library/techart/daotoadoupdate.htm
Migrating from DAO to ADO
http://msdn.microsoft.com/library/techart/daotoadoupdate_topic10.htm
New Features in ADO, ADOX, and JRO
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingmodifyingacc
esstables.htm
Creating and Modifying Access Tables
http://msdn.microsoft.com/library/officedev/odeopg/deovrcreatingaccessdataba
se.htm
Creating an Access Database
http://support.microsoft.com/support/excel/dao.asp
Using Data Access Object (DAO) in a Microsoft Excel Macro
Regards,
Tom Ogilvy
Scott <sw...@rcn.com> wrote in message
news:8e2f01c153a1$1049da00$19ef2ecf@tkmsftngxa01...
Philip Livingstone
Yes it is possible...we do it from Excel workbooks the
same as from VB, Java, or C++
if you have MDAC 2.1 + installed, you can use ADO (ActiveX
Data Objects). If you don't have this installed, you can
use DAO, which was originally specifically designed by
Microsoft for accessing Access databases...the DAO
components come free with Office I believe, and there is
Help for DAO in the Excel help file I think...
Here is an example for ADO (which is a free download from
the MSDN home page! look for "MDAC Install") - you need to
reference the ADO 2.* object-library from the References
dialogue in the VBE (Tools..References)...look for the
ActiveX Data Objects/ ActiveX Data Objects Recordset
library...
then ideally you'd create a Connection Object, and use
that as the connection for an ADODB Recordset object,
which you'd use for retrieving/updating data from the
database...
so here is some sample code for ADO, assuming your DB is
called Biblio.mdb, and is stored in a folder called C:\DB
(assuming User id="" and password="") - you'll need the
object libraries referenced before trying to run or debug
this code btw!
' >>>>> Example Code :
Sub TestData
Dim adoConn as ADODB.Connection
Dim adoRS as ADODB.Recordset
dim sConn as String
dim sSql as String
' This connectionstring tells ADO how to find and connect
to the DB and open it...
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Persist"
sConnect = sConnect & " Security Info=False;Data "
sConnect = sConnect & " Source=C:\db\BIBLIO.MDB"
' This SQL string will be used by the ADO Recordset
' object to query the database...
sSql="SELECT * FROM Authors"
' create DB connection objects ...
Set adoConn=New ADODB.Connection
Set adoRS=New ADODB.Recordset
' set properties of connection and open
With adoConn
.ConnectionString = sConnect
.CursorLocation=adUseClient
.Open
End With
With adoRS
.Source = sSql
.ActiveConnection = adoConn
.LockType = adLockPessimistic
.CursorType = adOpenStatic ' READ ONLY RS !!!
.Open
End With
' now you have a recordset of data...BUT READONLY !
' for an updatable recordset, change the "CursorType"
' property to one of the other options...
' this code will put each record into columns A, B, C of
' Sheet 1
Sheets("Sheet1").Select
Cells(1, 1).Select
' put a message box of the no of records..
MsgBox "No of Records=" & adoRS.RecordCount
With adoRS
' put the column names in...
ActiveCell.Value = .Fields(0).Name
ActiveCell.Offset(0, 1).Value = .Fields(1).Name
ActiveCell.Offset(0, 2).Value = .Fields(2).Name
Rows(1).Font.Bold = True ' make them Bold
ActiveCell.Offset(1, 0).Select ' go to the next row
Do Until .EOF
' put values of three fields of Rs in cells...
ActiveCell.Value = .Fields(0).Value
ActiveCell.Offset(0, 1).Value = .Fields(1).Value
ActiveCell.Offset(0, 2).Value = .Fields(2).Value
' move to next record....
.MoveNext
' drop down to next row of sheet...
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "Finished records, closing recordset..."
.Close ' close the recordset
Range("A1").Select
End With
' close the connection...
MsgBox "Closing connection to db", vbInformation
adoConn.Close
MsgBox "Killing objects, to release memory to Windows",
vbInformation
' Now destroy the objects...
Set adoRS = Nothing
Set adoConn = Nothing
End Sub
<<<<<< End of Example
I hope this helps, there are many many resources for
ADO on the MSDN site...using ADO you can also connect to
other databases like ORACLE, SQL Server, Sybase SQL
etc...depending on the OLE DB Provider used in the
connection string...
...also, look up 'Microsoft Data Link' (*.udl) files in
the help, MSDN...using these in the connectionstring you
can avoid hard-coding the location and name of the db...
like this:
...assuming the udl file is called "DB Connect.udl" and is
in a folder C:\DB ...
>>>> Code >>>>
adoConn.ConnectionString="FILE NAME=C:\DB\DB Connect.udl"
<<<< End Code <<<<
this is what we do in our organization, then if we need to
move the database or migrate it...we just need to modify
the UDL file ...
HTH, best of luck...
Philip Livingstone
>.
>