Getting data from Access to Excel

[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

 

[Daryl S]

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...

 

[Tom van Stiphout]

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

 

[Farhad]

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

 

[Daryl S]

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

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

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...