I got a malformed GUID error message when running following code, which
extract data from Access database and copy to Excel spreadsheet. The bug
comes up at rs.Data.Open szSQL, szConnect about the where clause, which I
copy from MS query.


Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset

szSQL = "SELECT db.ID, db.Market" _
& " WHERE (db.First_order_month={ts '2007-08-01 00:00:00'})"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\database.mdb;"

Set rsData = Nothing

'Where' clause with date was written incorrect, try this

Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset

szSQL = "SELECT db.ID, db.Market WHERE (db.First_order_month=#01/08/2007#)"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\database.mdb;"

Set rsData = Nothing


"Angus" wrote:

I got a malformed GUID error message when running following code, which
extract data from Access database and copy to Excel spreadsheet. The bug
comes up at rs.Data.Open szSQL, szConnect about the where clause, which I
copy from MS query.


Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset

szSQL = "SELECT db.ID, db.Market" _
& " WHERE (db.First_order_month={ts '2007-08-01 00:00:00'})"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\database.mdb;"

Set rsData = Nothing

perfect, thanks

"vbapro" wrote:

'Where' clause with date was written incorrect, try this

Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset

szSQL = "SELECT db.ID, db.Market WHERE (db.First_order_month=#01/08/2007#)"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\database.mdb;"

Set rsData = Nothing


"Angus" wrote:

I got a malformed GUID error message when running following code, which
extract data from Access database and copy to Excel spreadsheet. The bug
comes up at rs.Data.Open szSQL, szConnect about the where clause, which I
copy from MS query.


Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset

szSQL = "SELECT db.ID, db.Market" _
& " WHERE (db.First_order_month={ts '2007-08-01 00:00:00'})"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\database.mdb;"

Set rsData = Nothing