ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or... (1 Viewer)

[WhyACESS??????]

ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or...

Hello all

As you can see I'm having trouble with this error
ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or Update

I'm trying to transfer data from a Acess form to a Word table.
It is very simple, but for some reason it has a problem with Open method code If some one could look over what I've done, I would appreciate it greatly.

Private Sub cmdFlyer_Click()
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim rngInsertionPoint As Word.Range
Dim rstPets As New ADODB.Recordset
Dim strName As String
Dim strType As String
Dim strSize As String
Dim strDescription As String
 
Set appWord = New Word.Application
appWord.Documents.Add
Set docWord = appWord.Documents(1)
docWord.Application.Visible = True
 
Set rngInsertionPoint = docWord.Paragraphs(1).Range
rngInsertionPoint.Text = "Pets"
docWord.Paragraphs.Add
Set rngInsertionPoint = docWord.Paragraphs(2).Range
 
With rngInsertionPoint
    .Collapse Direction:=wdCollapseEnd
    .Tables.Add rngInsertionPoint, 1, 4
    End With
With rngInsertionPoint.Tables(1).Rows.Last
    .Cells(1).Range.Text = "Name"
    .Cells(2).Range.Text = "Type"
    .Cells(3).Range.Text = "Size"
    .Cells(4).Range.Text = "Description"
End With

[B]rstPets.Open "Pets", _
CurrentProject.Connection, adOpenStatic (Problem Area) [/B]
 
With rstPets
strName = .Fields("Name")
strType = .Fields("Type")
strSize = .Fields("Size")
strDescription = .Fields("Description")
End With
rngInsertionPoint.Tables(1).Rows.Add
With rngInsertionPoint.Tables(1).Rows.Last
.Cells(1).Range.Text = strName
.Cells(2).Range.Text = strType
.Cells(3).Range.Text = strSize
.Cells(4).Range.Text = strDescription
End With

 

[Banana]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

I don't see you providing rstPets a SQL statement....

The form should be something like

rstPets.Open "SELECT * FROM foo;"...

 

[WhyACESS??????]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

I'm new at transfering different applications to others. So this particular method is foregin to me. Could you elaborate a little?

 

[Banana]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

Do you have a table named "Pets"? If so, the syntax would be something like:

SELECT * FROM Pets;

which will retrieve all columns in that table (thought it looks like you only need four columns; Name, Type and Size, and Description, so the SQL can be rewritten as:

SELECT [Name], [Type], [Size], [Description] FROM Pets;

Note: the [] brackets are not really necessary, but in this it is because you're using Access's reserved words such as 'Name', which I would avoid using at all as it can confuse Access. A better name would be probably "PetName" for example. Google "Access reserved words lists" to get various list of what words you should not use in your columns and it will help you save on headaches.

As for SQL, if you go to Access's Query editor, then click Views -> SQL View, then it will show you the SQL statement that is created for a given query. You can play with the editor and see how SQL statement changes to help give you an idea of how SQL is formed.

HTH.

 

[WhyACESS??????]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

So would I keep
the

rstPets.Open "Pets", _
CurrentProject.Connection, adOpenStatic

Statement or totally erase it? Or do I insert it after Open?

 

[WayneRyan]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

Why,

rstPets.Open "Select * From Pets", CurrentProject.Connection, adOpenStatic

Wayne

 

[Banana]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

WayneRyan's got it. You do still need to declare the connection and cursor type.

 

[WhyACESS??????]

Re: ERROr code "Invalid SQL statement;expected "Delete, Insert, Procedure, Select, or

Okay so I tried it out but I think I noticed my problem.
I'm trying to extract a form, but this code is made to take data from the quieries.
How do I transfer my form data to the quierries?

rstPets.Open "Select* From Pets", _
CurrentProject.Connection, adOpenStatic