Help in connecting to MS Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

phong.lee@gmail.com - 27 Sep 2007 18:52 GMT

Hello all,

If anyone can guide me with the following, that would be great. I
have the following code that opens up a new workbook, now i can't
figure out how to get some data from a query that was built in MS
access. Any kind of sample would be greatly appreciated.

Private Sub CmdUpdateLiveDates_Click()

Dim strDatabaseName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rs As dao.Recordset

'Open new workbook and rename the worksheet tabs

Set wb = Application.Workbooks.Add
wb.Sheets(1).Name = "Live Dates By Month"
wb.Sheets(2).Name = "Live Dates By Portfolio"
wb.Sheets(3).Name = "Live Dates"

strDatabaseName = DatabasePath & "\" & DatabaseName

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Intialize database object
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Reply to this Message

GysdeJongh - 27 Sep 2007 20:24 GMT

> Hello all,
>
> If anyone can guide me with the following, that would be great. I
> have the following code that opens up a new workbook, now i can't
> figure out how to get some data from a query that was built in MS
> access. Any kind of sample would be greatly appreciated.

Hi phong.lee,
I made this demo for myself to find out how ADO works , maybe you can use
some of it :

============================================

Sub Transfer()
'
' Gys Mar-2007
'
'Demo of moving data between Excel and Access
'
'There must be a DSN definition for the Access data base People.mdb
'Cotrol Panel => Administrative Tools => Data Sources (ODBC) => Create
MyDSN.
'
'The database People.mdb must contain a Tabel Address.
'It is recommended that this tabel has a primary key.
'However this is not absolutely nesessary .The other fields are :
'Name and City.The table Address in the Data base People.mdb has 4 records
'
'The fields can be referred to by rstSource!City or rstSource.Fields("City")
'or rs.Fields(1) because City is the second field and the index runs from 0
'The data is from this workbook , Tab Output , Ranges FromDB and ToDB.
'
'This Subroutine uses early binding for Microsoft ActiveX Data Objects 2.8
Library
' Tools => References => Check the above Library
'
Dim I As Integer
Dim MySheet As Worksheet
Dim rstSource As New ADODB.Recordset
Dim rstDestin As New ADODB.Recordset
'
Set MySheet = ActiveSheet
'
rstSource.Open "Select * From Address ;", _
"Provider=MSDASQL;DSN=MyDSN", adOpenStatic, adLockOptimistic
'
'The adOpenStatic parameter is needed to establish the number of records
'with statements like rstSource.RecordCount
'
MsgBox "Number of records = " & rstSource.RecordCount
rstSource.MoveFirst
For I = 1 To 4
MySheet.Range("FromDB").Cells(I, 1) = rstSource.Fields("Name")
MySheet.Range("FromDB").Cells(I, 2) = rstSource.Fields("City")
rstSource.MoveNext
Next I
rstSource.Close
Set rstSource = Nothing
'
rstDestin.Open "Select * From Address ;", _
"Provider=MSDASQL;DSN=MyDSN", adOpenStatic, adLockOptimistic
For I = 1 To 4
rstDestin.AddNew
rstDestin.Fields("Name") = MySheet.Range("ToDB").Cells(I, 1)
rstDestin.Fields("City") = MySheet.Range("ToDB").Cells(I, 2)
rstDestin.Update
Next I
MsgBox "Number of records after adding new records = " &
rstDestin.RecordCount
rstDestin.Close
Set rstDestin = Nothing
End Sub

====================================

hth
Gys

Reply to this Message