get rid of "GUID" field

[groengoen]

I have a database of about 80 tables, each of which has a "GUID" field. I want to delete these fields, but this is not permitted by Access. The alternative seems to be to run a "make table" query on each table leaving out this field. I want to automate this somehow, as the manual version would be extremely tedious.
I thought that something like:

    tblCount = 0
    tblNotFound = False
    
    Dim DBName As String
    Dim DBase As Database
    Set DBase = CurrentDb

    Do Until tblNotFound
        
        DBName = DBase.TableDefs(tblCount).Name
        'All the tables have the prefix "tbl"
        If Left(DBName, 3) = "tbl"  Then
            'run a "make table" query for this table which copies all the fields
            'except the "GUID" field and puts on a prefix
        End If
        tblCount = tblCount + 1
        If DBase.TableDefs.Count = tblCount Then
            tblNotFound = True
        End If
    Loop

I don't really know what the code in the "If" statement would be.
Does anyone have any idea?

 

[thechazm]

Is the GUID field the first field of every table?

 

[groengoen]

no. It is the last field.

 

[groengoen]

By the prefix I mean onto the name of the new table (eg. if the table is "tblBill" call the new table "atblBill". I want to be able to delete all the original tables by using the "tbl" prefix.

 

[thechazm]

ok one last question does the tables your trying to work with have any relationships between each other?

 

[groengoen]

They do have some relationships, but that is not too important, as they can be deleted and recreated

 

[thechazm]

Ok I can help you do this but it won't be until later today before I can post anything as I am trying to finish another project before the end of the work day. What it will involve is opening up a recordset and stepping through each column to capture the column names. Have that build a make table string that will disregard the GUID field. Lastly if you want the data to be transfered over we will need an update query to pull the information from the old table to the new one. Its a few steps but definatly can be done.

So I'll touch back when I get a minute and we will knock this thing out.

 

[groengoen]

Thanks. I appreciate your help

 

[thechazm]

Ok I built a function that strips off the last field and builds a sql statement that makes the new tables beginning with the letter a. I have tested it quite a bit and I don't see any problems but as a precaution make a backup of the data before you run.

To use this just either in the debug window or make a button on the form and type the function name (RemoveGUID).

Place the code in a module.

Function RemoveGUID()
tblCount = 0
tblNotFound = False
 
Dim DBName As String
Dim DBase As Database, rs As DAO.Recordset, i As Long, bFirstPass As Boolean
Dim SQLString As String
Set DBase = CurrentDb
DoCmd.SetWarnings False
Do Until tblNotFound
 
    DBName = DBase.TableDefs(tblCount).Name
    'All the tables have the prefix "tbl"
    If Left(DBName, 3) = "tbl" Then
 
        Set rs = DBase.OpenRecordset("Select * from [" & DBName & "]", dbOpenDynaset) ' Sets the record source
        bFirstPass = True ' Keeps track if its the first time through the loop
        i = 0 ' Simple Counter
        Do While i <> rs.Fields.Count - 1 ' This goes through all fields except the last field which should be the GUID field
            If bFirstPass = True Then
                SQLString = "Select [" & DBName & "].[" & rs.Fields(i).Name & "]" ' First pass building of SQL statement
                bFirstPass = False
            Else
                SQLString = SQLString & ", [" & DBName & "].[" & rs.Fields(i).Name & "]" ' Still building SQL fields in statement
            End If
            i = i + 1 ' Next Field
        Loop
        SQLString = SQLString & " INTO [a" & DBName & "] FROM [" & DBName & "];" ' Sets up the last bit of the SQL statement
 
        DoCmd.RunSQL SQLString ' Executes the SQL statement
        rs.Close
    End If
    tblCount = tblCount + 1
    If DBase.TableDefs.Count = tblCount Then
        tblNotFound = True
    End If
Loop
MsgBox "Table operations complete!", vbInformation, "Yes!"
End Function

 

[groengoen]

That worked brilliantly, thanks a million

 

[thechazm]

No problem. We all get stuck sometimes.