Read access tables from inside excle using vb

[stoka]

i have several tables in access. we want excel reports produced that can access the Access tables, determine min max ave etc, and create the excel reports. i can either bring the data in to a scratch worksheet or do it in memory. have been able to get the ADODB connection but system stops when i try to get the recordset.



Sub GetData()

Dim conn As Object, rst As Object
Dim sql As String, myCnt As Long



sql = "SELECT [DateAndTime], [TagIndex], [Val] " & _
"FROM " & strDataFile1 & _
" WHERE [DateAndTime] >= " & datFirst & _
" AND [DateAndTime] <= " & datLast & ";"

Set conn = CreateObject("ADODB.Connection")

With cn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open strDataPath1
End With


Set rs = CreateObject("ADODB.Recordset")


With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic

********code bombs out here *******
** error message says it is trying to find my target file xxx.mdb but is
** looking for it at c:\documents and settings\steve\my
** documents\xxx.mdb.
** but my strDataPath1 is D:\XL_Access\xxx.mdb


myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
wsDataFile1.Range(Cells(1, 1), Cells(myCnt, 3)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing

Stop
End Sub

 

[shades]

Howdy. Not sure, but have you defined that path?

You have this:

.Open strDataPath1

but not defined. Perhaps I'm missing something.



NOTE: it is easier if you put code inside code tags (without the spaces)

[ c o d e ]

[ / c o d e ]
________
Xl200

 

[stoka]

Read Access tables inside excel

Yes the strDataPath1 is defined on a module level elsewhere. it is the complete path to the file to be opened.

 

[rsmonkey]

Create an excel template for the reults,
Create the neccessary queries,
Data > Import External Data > New Database Query

follow the wizard and the data will be entered into your template, depending on whether you have created your query in the correct format/structure.

The wizard should create the neccesary connection strings for you & shud look lsomething like this:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\YourDatabaseLocation.mdb;Mode=ReadWrite;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False