Table Creation Error

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Table Creation Error

Post by Steve_in_Kent »

Runtime error 3290.. on the create table line..
syntax error in the CREATE TABLE statement.

The crazy thing is.. it WAS working perfectly, creating the tables i needed.. i have no clue what i have changed to generate the syntax error

Code: Select all

Me.Command1.SetFocus

If IsNull(Me.Combo30) Then
    MsgBox ("Cannot create a tour with no name, sorry!"), vbCritical
    Call ClearThem
    GoTo EndyBit
End If

'On Error GoTo Hell

Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "CREATE TABLE " & Me.Combo30 & "(ID COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY, Qnumber INTEGER, Responsible_Person CHAR);"
dbs.Close
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Table Creation Error

Post by HansV »

Could it be that the value of Combo30 contains a space or punctuation? To avoid problems with those, use

Code: Select all

dbs.Execute "CREATE TABLE [" & Me.Combo30 & "] (ID COUNTER CONSTRAINT MyFieldConstraint PRIMARY KEY, Qnumber INTEGER, Responsible_Person CHAR);"
Best wishes,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Table Creation Error

Post by Steve_in_Kent »

ahh yes.. that is exactly it.. the value in combo 30 has a space !! gahhhhh
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Table Creation Error

Post by Steve_in_Kent »

When it creates the table Hans.. it doesnt actually immediately create the table..

is there some form of refresh i need to do.. to make it actually happen ?

the table, only appears when i go into design mode.
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Table Creation Error

Post by HansV »

Perhaps add

dbs.TableDefs.Refresh

(although that shouldn't be necessary)
Best wishes,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Table Creation Error

Post by Steve_in_Kent »

Thanks.. i was also googling to see how , once the table was created, i could make an individual field in that table, be a dropdown, from another table.. ie: its possible entries, are based on another table.
I will post the code that i found at some point.
do you know an easy way to do it ?
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Table Creation Error

Post by HansV »

Here is an example:

Code: Select all

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Products")
    Set fld = tdf.Fields("Category")
    Set prp = fld.CreateProperty("DisplayControl", dbInteger, acComboBox)
    fld.Properties.Append prp
    Set prp = fld.CreateProperty("RowSourceType", dbText, "Table/Query")
    fld.Properties.Append prp
    Set prp = fld.CreateProperty("RowSource", dbMemo, "SELECT CategoryID,Category FROM Categories")
    fld.Properties.Append prp
    Set prp = fld.CreateProperty("ColumnCount", dbInteger, 2)
    fld.Properties.Append prp
    Set prp = fld.CreateProperty("ColumnWidths", dbText, "0")
    fld.Properties.Append prp
    fld.Properties.Refresh
Best wishes,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 415
Joined: 04 Feb 2010, 11:46

Re: Table Creation Error

Post by Steve_in_Kent »

Thanks hans.. i found a snippet of this code on the net.. but this was the full Enchilada :)

Seems to work ok. (fingers crossed)
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Table Creation Error

Post by ChrisGreaves »

HansV wrote:
28 Jun 2020, 15:50
Could it be that the value of Combo30 contains a space or punctuation?
:clapping: :chocciebar: :cheers:
Chris
An expensive day out: Wallet and Grimace