Import Query from Access - Bug

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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?
Click to expand...
 
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 said:
Try this instead "Test Q"

J@Y said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
Try this instead "Test Q"

J@Y said:
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?
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Running UPDATE and APPEND queries from Access in Excel 1
Naming non-contigent cells with vba 5
Trying to write a query 1
Import Access data to text file rather than Excel 3
ADO - Access to Excel 5
ADO to pull data from Access query 3
SQL not importing records from ACCDB 2
Table format - connection string problem 1

Back
Top