Problems Grabbing Data from Access to Excel

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?
 
M

mp80237

Hi,

All dates. My last column is created by the following: TimeStamp:
(Format([StartTime],"Short Time")) & "-" & (Format([EndTime],"Short
Time")) & "MDT/" & (Format([StartGMT],"Short Time")) & "-" &
(Format([EndGMT],"Short Time")) & "GMT/" & (Format([StartHK],"Short
Time")) & "-" & (Format([EndHK],"Short Time")) & "HKG/" &
(Format([StartSyd],"Short Time")) & "-" & (Format([EndSyd],"Short
Time")) & "SYD"

Martha

Hi
I tried to replicate your code and query. I got the error if one of the data
types that you are trying to grab is an Ole Object.
Can you check and let me know the data types of all the columns that the
query is pulling?

Alok

mp80237 said:
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?
Click to expand...
Click to expand...
 
M

mp80237

Hello,

I found the problem. Sorry for wasting your time. There were a few
errors from null values. Just have to let the team know they need to
fill out everything. Thank you so much for the help!

Hi,

All dates. My last column is created by the following: TimeStamp:
(Format([StartTime],"Short Time")) & "-" & (Format([EndTime],"Short
Time")) & "MDT/" & (Format([StartGMT],"Short Time")) & "-" &
(Format([EndGMT],"Short Time")) & "GMT/" & (Format([StartHK],"Short
Time")) & "-" & (Format([EndHK],"Short Time")) & "HKG/" &
(Format([StartSyd],"Short Time")) & "-" & (Format([EndSyd],"Short
Time")) & "SYD"

Martha

Hi
I tried to replicate your code and query. I got the error if one of the data
types that you are trying to grab is an Ole Object.
Can you check and let me know the data types of all the columns that the
query is pulling?

Alok

mp80237 said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing Data to Excel 2007 from Access 2007 5
Importing Data to Excel 2007 from Access 2007 1
copying field names from Access query 1
ADO recordset 2
ADO - Access to Excel 5
How to copy data from an excel cell into a field in access table 0
Search Column for matches to a Range of Data, then modify cell 1
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0

Top