Issue creating a table in code

[Newby]

I am try to duplicate creating a table based on eHow Tech info. I have entered and then tried copying the offending lines directly into my code, but continue to get the error message below.

Private Sub To_DoQuery()
Dim RecordSt As Recordset
Dim dBase As Database
Dim stringSQL As String
Dim rCnt As Integer

stringSQL = "CREATE TABLE CityTbl (City TEXT(25), State TEXT(25));"
DoCmd.RunSQL (stringSQL)

‘’’ When I try to run this query I get the following message “ run-time error 3290: Syntax error in create table statement.” I copied this statement directly from ‘eHow tech’ http://www.ehow.com/how_7148832_acce...y-results.html





DoCmd.RunSQL (stringSQL)


stringSQL = "inset into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Fort Worth', 'Texas');"
DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)
stringSQL = "inset into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Dallas', 'Texas');"
DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)

End Sub

[alansidman]

I copied your code inserted into a blank database and it worked for me once I corrected the spelling on the Insert Lines. You were missing an 'r'.

Alan

[Newby]

The spelling was not the original issue. The program never had a chance to get that far. I had missing dll and had to download a fix, and with the fix and the spelling adjust, the program create a table successfully. Now when a add the remaining code and add a query, I get the following "Object variable or with block variable not set". See attached code. Again I copied the offending code directly of the hyperlink.

Private Sub To_DoQuery()
Dim RecordSt As Recordset
Dim dBase As Database
Dim stringSQL As String
Dim rCnt As Integer


'stringSQL = "CREATE TABLE CityTbl (City TEXT(25), State TEXT(25));"
'DoCmd.RunSQL (stringSQL)

stringSQL = "insert into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Fort Worth', 'Texas');"
DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)
stringSQL = "insert into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Dallas', 'Texas');"
DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)


stringSQL = "SELECT CityTbl.* FROM CityTbl;"

Set RecordSt = dBase.OpenRecordset(stringSQL)

‘ !!!!!!!!!!!!!!!!! Run-time error 91 (On line above)

‘!!!!!!!!!!!!!!!!!!!!!!Object variable or with block variable not set

RecordSt.MoveFirst
For rCnt = 0 To RecordSt.RecordCount
MsgBox (RecordSt.Fields("City").Value & "," & Rcordst.Fields("state").Value)
RecordSt.MoveNext
Next rCnt

End Sub


Thanks for your help.

[orange]

You are having a problem with spelling (missing an e in RcordSt

You also have not Set dBase =Currentdb
You should avoid DoCmd.SetWarnings False ' this masks good feedback
Instead of DoCmd.RunSQL (stringSQL) use dbase.execute (stringSQL,dbFailOnError) which will only give a message if an error occurs

I replaced your For Loop with a Do While Loop (just seems cleaner to me) you can do as you wish.

Openrecordset gives position on first record (if there is one).

Sub To_DoQuery()
Dim RecordSt As Recordset
Dim dBase As Database
Dim stringSQL As String
Dim rCnt As Integer


stringSQL = "CREATE TABLE CityTbl (City TEXT(25), State TEXT(25));"
DoCmd.RunSQL (stringSQL)

stringSQL = "insert into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Fort Worth', 'Texas');"
'DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)
stringSQL = "insert into citytbl([City],[State])"
stringSQL = stringSQL & "Values('Dallas', 'Texas');"
'DoCmd.SetWarnings False
DoCmd.RunSQL (stringSQL)


stringSQL = "SELECT CityTbl.* FROM CityTbl;"
Set dBase = CurrentDb
Set RecordSt = dBase.OpenRecordset(stringSQL)

' !!!!!!!!!!!!!!!!! Run-time error 91 (On line above)

'!!!!!!!!!!!!!!!!!!!!!!Object variable or with block variable not set
Do While Not RecordSt.EOF
'RecordSt.MoveFirst
'For rCnt = 0 To RecordSt.RecordCount
MsgBox (RecordSt.Fields("City").Value & "," & RecordSt.Fields("state").Value)
RecordSt.MoveNext
'Next rCnt
Loop
End Sub

As you can see, attention to detail is critical.
You may wish to investigate debugging techniques at
http://www.cpearson.com/excel/debug.htm

[Newby]

thank it worked. i tried to adopt your dbase.execute command, but I have a compile error Expect:=.

[orange]

see http://msdn.microsoft.com/en-us/libr.../ff197654.aspx

I believe I had brackets around the parameters in my last post--- that is not correct

general syntax

dbsNorthwind.Execute strSQLRestore, dbFailOnError