Please I need help! I have a table in Access 97 and I would like to run a macro to query this table and pull the data into Excel. The code itself was modified from one by Andy Wiggins.This should be a real easy macro to write, but I keep getting all kinds of runtime errors. This is the latest error:-

Error 3075 : Syntax error, (missing operator) in query expression.

I have tried everything...Any help will be appreciated!

Here is my code:

'' Originally Written : 19-Oct-1999 by Andy Wiggins - Byg Software Ltd

Sub Selectfromtable2()

Dim dbs As Database
Dim rs As Recordset
Dim Ws As Worksheet
Dim Path As String
On Error GoTo ErrorHandler

ThisWorkbook.Activate
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
''Open the database
Path = "C:\Documents and Settings\testu\Desktop\Rtest.mdb"
Set dbs = OpenDatabase(Path)

Set rs = dbs.OpenRecordset("SELECT * " & _
"FROM [Pretrial]" & _
"WHERE [Pretrial].[Name] = 'John'" & _
"AND [Pretrial].[Age]= 35" & _
"ORDER BY [Pretrial].[Score];")

For i = 0 To rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
Next

'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).

Ws.Range("A2").CopyFromRecordset rs

'This next code set will just select the data region and auto-fit
'the columns
Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -

lbTidy:
dbs.Close
Set dbs = Nothing
Set rs = Nothing

Exit Sub
'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ErrorHandler:

vtMessage = "Table and data creation error"
vtMessage = vtMessage & _
Chr(10) & _
Chr(10) & "Error Number: " & Err & _
Chr(10) & "Error Description: " & Error()

MsgBox vtMessage, vbInformation, ctByg
Resume lbTidy
End Sub

Which line is throwing the error?

Could it be the missing spaces in the SQL statement? I'm not sure how forgiving it is to omit spaces.

Well spotted. There are a number of missing spaces in the statements, one of which will parse but should still be adjusted. +1 point for knowing which one is accepted by the parser?

mind you it could be somehting kanky n Excel.. as strictly speaking it an Excel / JET problem.. it has nothing what so ever to do with Access.

for the record though I'd agree its almost certainly going to be the SQL that is the problem, (for now)

[/code]Set rs = dbs.OpenRecordset("SELECT * FROM [Pretrial]" & _
" WHERE [Pretrial].[Name] = 'John'" & _
" AND [Pretrial].[Age]= 35" & _
" ORDER BY [Pretrial].[Score];")[/code]

stylewise I'suggest you put a leading space in things such as multiline SQL statements, as its marginally easier to spot in my books

Id also suggest that when you are opening SQL statements, assign the SQL to a variable so that its easy to debug the SQL by repeating it back to the screen in a msgbox, or setting a break point in the code so I can see what the SQL engine is being requested to do, as opposed to what I think the code is doing

eg
[/code]dim strSQL as string
strSQL = "SELECT * FROM [Pretrial]" & _
" WHERE [Pretrial].[Name] = 'John'" & _
" AND [Pretrial].[Age]= 35" & _
" ORDER BY [Pretrial].[Score];"
msbox strsql,vbinformation,"SQL statement is....."
Set rs = dbs.OpenRecordset(strSQL)
[/code]

Thanks to all for the input. I made the changes with regards to the spaces inthe SQL statement and I get another error noW;

Run-Time error '91': Object variable or With block variable not set

Debugger is pointing to this line in the code:
Ws.Cells(1, i + 1).Value = rs.Fields(i).Name

When I change my view of the VBA screen to the "locals window"- these are the values :
Ws = Nothing

Under connection, the value for dbs = <opertion is not supported for this type of object>

I am inclined to believe that my recordset is not being created in the first place.'

I don't know what esle to do. I tried other forums and it seems that no one really has a clear cut working solution for querying access tables using VBA code.

Any additional input will be appreciated. thanks!
__________________________________________________ ___________

well you are now into the realms of Excel not Access... so its probably best to ask this question in the Excel thread on this forum

Seems to me that WS is never set.

You probably need a line such as Set Ws = CurrentWorksheet or something. I have no idea of the syntax or specifics though since I'm an Access man

Hi

Just to dot the i's and cross t's

I think it should be

Set ws = ActiveSheet

Also, if it is the active sheet to be written to (as is implied), then you can dispence with

dim Ws
Set ws = ActiveSheet

and just use

Cells(1, i + 1).Value = rs.Fields(i).Name

Although when you have multiple workbook open it can be better (code easier to follow) if references are explicitly set the different wooks/sheets (this is preferable to activating the books/sheet as required).


MTB

That was the problem! I can't believe I missed that . It's the little things that get us. Once I set Ws = activesheet, it worked like a miracle.

Thank you!

And Thanks to all others for their input!