Getting data from Access to Excel

  • Thread starter Thread starter Farhad
  • Start date Start date
F

Farhad

Hi,

i don't know if any body can helps me on this issue i wrote a code as below
but no data goes to recordset i think this is because of date format or
something like this because i can copy the fieds name the vaiables FrstDate
shows "12/31/2009" and LstDate shows "1/28/2010" please help.

Sub bbb()
Dim rst As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim str1 As String
Dim sID As String
Dim iCost As Integer
str1 = ""
sID = ""
Dim sPN As String
sPN = ""
Dim i As Integer
Dim j As Integer
iCost = 0
If Now - 31 < "12/31/2009" Then
FrstDay = "12/31/2009"
Else
FrstDay = Left(Now - 31, 9)
End If
LstDate = Left(Now, 9)
str1 = "Driver={MySQL ODBC 3.51
Driver};Server=192.168.1.155;DATABASE=DSS;UID=root;option =
1+2+8+32+2048+163841"
conn.CursorLocation = adUseClient
conn.ConnectionString = str1
conn.Open
rst.Open "select * from big where R_Date >" & FrstDay & " and R_Date <" &
LstDate, conn
rstrows = rst.RecordCount
For iCols = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, rst.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
conn.Close
End Sub
 
Farhad -

It looks like you are treating your dates are text. FrstDay and LstDay
should be dimensioned as dates and the date delimiter should be used. Like
this:

Sub bbb()
Dim rst As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim str1 As String
Dim sID As String
Dim iCost As Integer
str1 = ""
sID = ""
Dim sPN As String
sPN = ""
Dim i As Integer
Dim j As Integer
Dim FrstDay as Date
Dim LstDate as Date

iCost = 0
If Now - 31 < #12/31/2009# Then
FrstDay = #12/31/2009#
Else
FrstDay = Now() - 31
End If
LstDate = Now()
str1 = "Driver={MySQL ODBC 3.51
Driver};Server=192.168.1.155;DATABASE=DSS;UID=root;option =
1+2+8+32+2048+163841"
conn.CursorLocation = adUseClient
conn.ConnectionString = str1
conn.Open
rst.Open "select * from big where R_Date > #" & FrstDay & "# and R_Date < #"
& _
LstDate & "#", conn
rstrows = rst.RecordCount
For iCols = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, rst.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
conn.Close
End Sub

Try it and let us know...
 
Are you sure about that driver statement? That last number should
probably 16384.

As an experiment remove the date criteria:
rst.Open "select * from big", conn

Date values more than likely should be wrapped in single-quotes.
"...where R_Date>'" & FrstDay & "'..."

-Tom.
Microsoft Access MVP
 
Hi Daryl,

Thanks for your help but i got an error on the line rst.Open... and i think
it is related to the variable LstDate because when i check it, it shows date
with time so how can i get off the time from the variable? the error is
talking abot it too read error below:
Run-time error '-2147217900(80040e14)': [MySQL][ODBC 3.51
Driver][MySqld-4.0.13-ht] you have an error in your SQL syntax. Check the
manual that correspods to your MySQL server version for the right syntax to
use near '1:41:28PM' at line 1
 
Farhad -

If you are using mySQL, then the date delimeter is probably different then
in Access. You might need single quotes or a different format for the date
for the back-end you are using.
--
Daryl S


Farhad said:
Hi Daryl,

Thanks for your help but i got an error on the line rst.Open... and i think
it is related to the variable LstDate because when i check it, it shows date
with time so how can i get off the time from the variable? the error is
talking abot it too read error below:
Run-time error '-2147217900(80040e14)': [MySQL][ODBC 3.51
Driver][MySqld-4.0.13-ht] you have an error in your SQL syntax. Check the
manual that correspods to your MySQL server version for the right syntax to
use near '1:41:28PM' at line 1
--
Farhad Hodjat


Daryl S said:
Farhad -

It looks like you are treating your dates are text. FrstDay and LstDay
should be dimensioned as dates and the date delimiter should be used. Like
this:

Sub bbb()
Dim rst As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim str1 As String
Dim sID As String
Dim iCost As Integer
str1 = ""
sID = ""
Dim sPN As String
sPN = ""
Dim i As Integer
Dim j As Integer
Dim FrstDay as Date
Dim LstDate as Date

iCost = 0
If Now - 31 < #12/31/2009# Then
FrstDay = #12/31/2009#
Else
FrstDay = Now() - 31
End If
LstDate = Now()
str1 = "Driver={MySQL ODBC 3.51
Driver};Server=192.168.1.155;DATABASE=DSS;UID=root;option =
1+2+8+32+2048+163841"
conn.CursorLocation = adUseClient
conn.ConnectionString = str1
conn.Open
rst.Open "select * from big where R_Date > #" & FrstDay & "# and R_Date < #"
& _
LstDate & "#", conn
rstrows = rst.RecordCount
For iCols = 0 To rst.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
Next
ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, rst.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset rst
rst.Close
conn.Close
End Sub

Try it and let us know...
Click to expand...
Click to expand...
 
Back
Top