I have this code:

Dim Data1 As ADODB.Recordset
Dim Connect As String

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set Data1 = New ADODB.Recordset
Data1.Open "[Test Q]", Connect

Sheets(1).Range("A1").CopyFromRecordset Data1

The Query "Test Q" works in Access and I get an output there. But when I run
it in here, nothing is copied over. The script works if I put a table in
there. Why is this happening?

Try this instead "Test Q"

"J@Y" wrote:

I have this code:

Dim Data1 As ADODB.Recordset
Dim Connect As String

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set Data1 = New ADODB.Recordset
Data1.Open "[Test Q]", Connect

Sheets(1).Range("A1").CopyFromRecordset Data1

The Query "Test Q" works in Access and I get an output there. But when I run
it in here, nothing is copied over. The script works if I put a table in
there. Why is this happening?

My code for Test Q:

SELECT *
FROM VTABLE
WHERE VTABLE.ID between (Select VTABLE.ID
FROM VTABLE
Where VTABLE.Account = "2")
AND
(Select MIN( VTABLE.ID)
From VTABLE
WHERE VTABLE.Name like "*000000000*" AND VTABLE.ID ( Select VTABLE.ID
FROM VTABLE
Where VTABLE.Account = "2"));


I think something doesn't work for excel here, I tried breaking apart this
into parts and each part worked.....


"Mike" wrote:

Try this instead "Test Q"

"J@Y" wrote:

I have this code:

Dim Data1 As ADODB.Recordset
Dim Connect As String

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set Data1 = New ADODB.Recordset
Data1.Open "[Test Q]", Connect

Sheets(1).Range("A1").CopyFromRecordset Data1

The Query "Test Q" works in Access and I get an output there. But when I run
it in here, nothing is copied over. The script works if I put a table in
there. Why is this happening?

After alot of testing, the problem was Excel VB doesn't recognize Like
"*0000000*".
Instead of *, Excel uses %. This makes the Query not work in Access, but
works in Excel.

"J@Y" wrote:

My code for Test Q:

SELECT *
FROM VTABLE
WHERE VTABLE.ID between (Select VTABLE.ID
FROM VTABLE
Where VTABLE.Account = "2")
AND
(Select MIN( VTABLE.ID)
From VTABLE
WHERE VTABLE.Name like "*000000000*" AND VTABLE.ID ( Select VTABLE.ID
FROM VTABLE
Where VTABLE.Account = "2"));


I think something doesn't work for excel here, I tried breaking apart this
into parts and each part worked.....


"Mike" wrote:

Try this instead "Test Q"

"J@Y" wrote:

I have this code:

Dim Data1 As ADODB.Recordset
Dim Connect As String

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set Data1 = New ADODB.Recordset
Data1.Open "[Test Q]", Connect

Sheets(1).Range("A1").CopyFromRecordset Data1

The Query "Test Q" works in Access and I get an output there. But when I run
it in here, nothing is copied over. The script works if I put a table in
there. Why is this happening?

"J@Y" wrote:

I have this code:

Dim Data1 As ADODB.Recordset
Dim Connect As String

Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set Data1 = New ADODB.Recordset
Data1.Open "[Test Q]", Connect

Sheets(1).Range("A1").CopyFromRecordset Data1

The Query "Test Q" works in Access and I get an output there. But when I run
it in here, nothing is copied over. The script works if I put a table in
there. Why is this happening?