ADO for Access

[Kathy]

Hi,

I 've got problem opening a Table which I've just created.
Here is what I did:

cmd.CommandText = "Create Table "Employees" (Name char(255),Adrr char(255))
cmd.Execute

--->Here if i go to my database, I can see "Employees" table there.
--->Now, I want to open this table to add records to it

rs.Open "Employees", cnn,,,adCmdTable

---> I 've got error
Note: I have no problem opening other Tables which already exists
in the database. Only with new Tables that problem occurs.........

THanks

[Eric Gravert]

Can you give the specifics of what the error is? Also, what is the cursortype and locking type for the recordset?

[Kathy]

Hi Eric,

This is what I've got:

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
-2147217900

and this is how I open:
lstrCommand = "select * from Employees"
rs.Open lstrCommand, cnn, adOpenStatic, adLockOptimistic

Thanks

[Eric Gravert]

Here is the exact code that I am using to re-impliment your problem but I can't seem to get it break. Figures allways bugs in a person's code till you try and break it!! Well look at this and see if there is anything that helps or anything I am doing to prevent the error from showing up.
Hope this helps.


option Explicit

private Sub Command1_Click()
Dim con as Connection
Dim rec as Recordset
Dim cmd as Command
Dim cmdSt as string

set con = new Connection
con.Open "DSN=Biblio" 'just using the biblio database to save time

set cmd = new Command
cmd.ActiveConnection = con

cmd.CommandText = "Create Table ""Employees"" (Name char(255),Adrr char(255))"
cmd.Execute
set cmd = nothing

cmdSt = "select * from Employees"

set rec = new Recordset
rec.CursorLocation = adUseClient
rec.Open cmdSt, con, adOpenStatic, adLockOptimistic
rec.ActiveConnection = nothing

If rec.EOF then
Debug.print "No records returned"
else
Debug.print "Records returned"
End If

set con = nothing
set rec = nothing

End Sub

[Kathy]

Thanks Eric,
The problem is I named my table as "Option".
I guess it's a reserved word or whatever.
After I changed the name of table, then everything is OK