M
mp80237
Hello,
In my excel spreadsheet I have a vb script to grab data from a access
database I created.
Sub Time_Stamp()
Dim db As DAO.Database, qry As DAO.QueryDef, rec As DAO.Recordset
Dim ST As String
Dim i As Integer
Dim R As Long
Dim C As Integer
Path = "R:\ Incidents.mdb"
' set path to Enrollment path"
'now get data from Access
Set db = DBEngine.Workspaces(0).OpenDatabase(Path) ' Open database
'Get data for TimeStamp
Set qry = db.QueryDefs("Q-Time_Stamp")
Set rec = qry.OpenRecordset
Sheets("Stamp").[a3].CopyFromRecordset rec
For Counter = 0 To rec.Fields.Count - 1
Worksheets("Stamp").[a2].Offset(0, Counter).Value =
rec.Fields(Counter).Name
Worksheets("Stamp").[a2].Offset(0, Counter).Font.Bold = True
Next Counter
End Sub
A example of one line from Microsoft Access:
iStartDate iEndDate TimeStamp
02-Jan-06 02-Jan-06 00:02-02:19MDT/07:02-09:19GMT/15:02-17:19HKG/18:02-20:19SYD
When I run my macro in Excel I get a run-time error '-2147467259
(80004005)': Method 'CopyFromRecordset' of object 'Range'
failed
When I remove the field [TimeStamp], it pulls in just fine, but of
course that is what I really need. A co-worker thinks it is because of
the slashes. I have tried putting a '(' around it and does not
work. Any ideas?
In my excel spreadsheet I have a vb script to grab data from a access
database I created.
Sub Time_Stamp()
Dim db As DAO.Database, qry As DAO.QueryDef, rec As DAO.Recordset
Dim ST As String
Dim i As Integer
Dim R As Long
Dim C As Integer
Path = "R:\ Incidents.mdb"
' set path to Enrollment path"
'now get data from Access
Set db = DBEngine.Workspaces(0).OpenDatabase(Path) ' Open database
'Get data for TimeStamp
Set qry = db.QueryDefs("Q-Time_Stamp")
Set rec = qry.OpenRecordset
Sheets("Stamp").[a3].CopyFromRecordset rec
For Counter = 0 To rec.Fields.Count - 1
Worksheets("Stamp").[a2].Offset(0, Counter).Value =
rec.Fields(Counter).Name
Worksheets("Stamp").[a2].Offset(0, Counter).Font.Bold = True
Next Counter
End Sub
A example of one line from Microsoft Access:
iStartDate iEndDate TimeStamp
02-Jan-06 02-Jan-06 00:02-02:19MDT/07:02-09:19GMT/15:02-17:19HKG/18:02-20:19SYD
When I run my macro in Excel I get a run-time error '-2147467259
(80004005)': Method 'CopyFromRecordset' of object 'Range'
failed
When I remove the field [TimeStamp], it pulls in just fine, but of
course that is what I really need. A co-worker thinks it is because of
the slashes. I have tried putting a '(' around it and does not
work. Any ideas?