CreateTable (if doesn't exist) 2

[lars7]

Hi,
I'm trying to combine a couple of todays posts with "CreateQueryDef" code that I have to Create a table (if it doesn't already exist) but it is going horribly wrong. It starts of badly by not getting past the first 2 "Dim's" with error message "user-defined type not defined" and then with those 2 commented out it stops at "TableExists" with another "ambiguous name detected: TableExists" message. Could anyone please point out to me where I am going wrong and maybe offer some advise as to how to fix it. Thanks.

The Code;

Private Sub Command64_Click()
Dim db As Database
Dim tdf As TableDef
Dim strSQL As String

Set db = CurrentDb

If Not TableExists("tblPcNew") Then
Set tdf = db.CreateTableDef("tblPcNew")
Else
Set tdf = db.TableDefs("tblPcNew")
End If

strSQL = "INSERT INTO tblPcQueryNew " & _
"From tbltruststaff "

' Pass the SQL string to the query
tdf.SQL = strSQL

Set tdf = Nothing
Set db = Nothing

End Sub

and the Module:

Function TableExists(strTableName As String) As Boolean
' This procedure returns True or False depending on whether
' the table named in strTableName exists.
Dim dbs As Database, tdf As TableDef

On Error Resume Next
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTableName)
If Err = 3265 Then
' Table does not exist.
TableExists = False
Else
' Table exists.
TableExists = True
End If
Err = 0
End Function

Tom.

 

[Remou]

For 'user type not defined', have a look at your references to make sure you have Microsoft DAO x.x Object Library checked. Then alter your dims so:
Dim db As DAO.Database
Dim tdf As DAO.TableDef

For your 'ambiguous name', have a look to see if you have TableExists as the name of another sub, function or module.

As for the rest, some of it looks like you are trying to create a query, some that you are trying for a new table. Which would you like?

 

[lars7]

Hi again Remou,
I checked the reference to Dao, changed the 2 Dim's and looked to see, but as I suspected, there is no other TableExists of any description in my database. I have changed the code slightly to remove any reference to the query that I seen as it is definetly a table that I would like to create. At the moment I am getting a Syntax error with this code.

Private Sub Command64_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim SQL As String

Set db = CurrentDb

If Not TableExists("tblPcNew") Then
Set tdf = db.CreateTableDef("tblPcNew")
Else
Set tdf = db.TableDefs("tblPcNew")
End If

SQL = "INSERT INTO tblPcQueryNew " & _
"From tbltruststaff "

CurrentProject.Connection.Execute SQL

Set tdf = Nothing
Set db = Nothing
End Sub

With this line highlighted yellow:

CurrentProject.Connection.Execute SQL

 

[lars7]

Hi remou,
It was my "Sql = Insert into" that was the problem, it should have been "SQL = "Select * INTO", I have it working now.
Thanks for the help again.


Private Sub Command64_Click()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim SQL As String

Set db = CurrentDb

If Not TableExists("tblPcNew") Then
Set tdf = db.CreateTableDef("tblPcNew")
Else
Set tdf = db.TableDefs("tblPcNew")
End If

SQL = "Select * INTO tblPcQueryNew " & _
"From tbltruststaff "

CurrentProject.Connection.Execute SQL

Set tdf = Nothing
Set db = Nothing
End Sub

 

[MichaelRed]

To determine that an object (not specifically a table) exist, two procedures and one query shown below:

The first is the primary function. It includes the SQL string for the query, which needs to be created / copied into the queries of the database.

The second is used in the query to test the type of the object.

Usxers should be aware that MS does not include 'MSysObjecs' in the list of "Documented" objects for MSysObjects and thus MAY change the content without notice. The specific fields referenced in these procedures have not changed since I became aware of them (circa 1994), but that does not mean they will remain throughout the reainder of the lifetime of Ms. Access. There are additional object types identified in the MSysObjects table, so users with other interests may choose to review the available literature (see search / advanced search) and avail themselves of additional information.

As is common in these fora, there is no error chacking included in the procedures, assuming the users will supply any desired.

Public Function basObjExists(strObjIn, strObjType) As Integer

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    strQdfSQL

    'When the Object is found in the specified object collection _
     ? strObjName(tblProcedures", "Table") _
     1

    'When the Object is NOT found in the specified object collection _
     ? basObjExists("tblProcedures", "Form") _
     0

    strQdfSQL = "SELECT MSysObjects.Name, basObjType([Type]) AS TypeName " _
              & "FROM MSysObjects " _
              & "WHERE (((MSysObjects.Name)=[strObject]) AND " _
              & "((basObjType([Type]))=[strType]));"

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryMS_ObjectExist")
    qdf.Parameters("strObject") = strObjIn
    qdf.Parameters("strType") = strObjType
    Set rst = qdf.OpenRecordset
    
    basObjExists = rst.RecordCount

End Function

Public Function basObjType(ObjType As Variant) As Variant

    'ObjType = -32764

    basObjType = Switch(ObjType = -32768, "Form", _
                    ObjType = -32764, "Report", _
                    ObjType = -32761, "Module", _
                    ObjType = 1, "Table", _
                    ObjType = 5, "Query")

End Function

MichaelRed

 

[Remou]

Hi Lars
I think you will find that this:

If Not TableExists("tblPcNew") Then
  Set tdf = db.CreateTableDef("tblPcNew")
Else
  Set tdf = db.TableDefs("tblPcNew")
End If

Is not serving any useful purpose. The new table is created by these two lines:

SQL = "Select * INTO  tblPcQueryNew " & _
    "From tbltruststaff "
    
CurrentProject.Connection.Execute SQL

Whether tblPCNew exists or not. Are you happy with that?

 

[lars7]

Hi Remou,
Yes your right, I tested it at first by deleting and recreating the table and thought it was fine but I am getting an error message if the table already exists. I have tried the following code too but I am getting the same message. I like the look of Michaels code, but I'm struggling to understand the query part of it.

Public Function TableExists(TableName As String) As Boolean
' Checks for the existence of a table (named as TableName)
' and returns true if table exists.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
TableExists = False
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = TableName Then
TableExists = True
Exit Function
End If
Next tdf
End Function

 

[PHV]

A shorter version I've already posted:
Public Function IsTable(strTableName As String) As Boolean
On Error Resume Next
IsTable = (CurrentDb.TableDefs(strTableName).Name = strTableName)
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
I am Still getting the error message "Table tblPcNew already exists" I was hoping that if the table did exist then only the information contained in it would be updated like my query, am I thinking right or would it be better just to delete the table and create a new one.

 

[Remou]

Do you really need a new table? Wouldn't a query work ok?

 

[lars7]

Hi Remou,
I started of just wanting to learn how to create a table through code as I have been working with creating queries that way all week with some success. I thought that checking to see if it already existed, like I've been doing with the queries, was necessary but I can't think of any purpose for it at the moment other than wanting to know how to do it. Thanks to everyone for the help today, I will go now and mess about with the code I have and see what else I can learn.

Tom.

 

[Remou]

Sound like a good reason. Have fun.