Create new table IF "Variable" Not In List "TBLNames" 2

[Varga]

Hello,
I have created a variable that records the names of all the tables in my database. (i.e. TBLNMLST)
I also have a Variable that is unique to each user. (i.e. USRID)
I want to check the USRID variable against the TBLNMLST variable to see if there is an existing table in the database, with the same name as the USRID value.
If the value of USRID does not match any of the table names in the database, then I want to create a table with the USRID value as it's name.
The problem I'm having is with the setup of the "IF...NOT IN LIST...THEN" statement. How can I properly create this statement so that I can trigger the creation of a new table?

Dim T As TableDef, TBLNMLST As String, DB
Set DB = CurrentDb
For Each T In DB.TableDefs
TBLNMLST = TBLNMLST & T.Name & ";"
Next

If USRID Not In (" & TBLNMLST & &quot Then...

I can make this type of statement work within a SQL "SELECT" query, but how can I create this kind of statement to trigger the creation of a table???

Please let me know,
Thanks,
Blair

 

[mpastore]

You can use the hidden system table to accomplish this:

Sub user_table()
Dim USRID As String

USRID = CurrentUser

Dim sSQL As String
sSQL = "SELECT MSysObjects.Name, MSysObjects.Type " _
& " FROM MSysObjects " _
& &quot; WHERE (((MSysObjects.Name)='<<USERID>>') AND ((MSysObjects.Type)=1));&quot;

sSQL = Replace(sSQL, &quot;<<USERID>>&quot;, USRID)

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(sSQL)

If rs.EOF Then
CurrentDb.Execute (&quot;create table &quot; & USRID & &quot; (foo text(1))&quot
End If

End Sub


Mike Pastore

Hats off to (Roy) Harper

 

[Varga]

Thank you very much, that did work well.

I've never used the sSQL = Replace(sSQL, &quot;<<USERID>>&quot;, USRID) method. Could you please explain exactly what this 'Replace' method accomplishes? (It worked fine, I just want to know.)

Also, I'm trying to create the table with VB code and I don't know how to create a Yes/No data type field.

(Example)
Set DB = CurrentDb
Set T = DB.CreateTableDef(USRID)
Set F = T.CreateField(&quot;Manually&quot;, dbText) '(yes/no)
T.Fields.Append F

I don't know what to use in place of dbText.
Please let me know,
Thank you for your help so far.
Blair

 

[mpastore]

replace() is just an internal VBA function that will take a string and replace characters that you have specified. Simple example:

?replace(&quot;Hello World&quot;,&quot;l&quot;,&quot;p&quot
yields

&quot;Heppo Worpd&quot;

Very useful, we used to have to write custom functions for this in Access version 97 (?) and prior.

---------------------------------------
syntax for yes/no field in DDL is yesno. For example:

create table foo (myyn yesno)

For fields collection I believe it is dbBoolean

Mike Pastore

Hats off to (Roy) Harper

 

[Varga]

Thank you very much for your help.
I really like this site and it's helpful content.

My program now works the way I want it to.
I would still be stuck, without your help.

Mike,
Once again,
Thank you for your assistance.
Blair

(Hats off to (Roy) Harper?)