Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Create tables in access db (2016) using vba

I have a db with a data table and I want to create a table for each field to store data changes. the data table CAPDATA has many fields so I wanted to create a routine that would create the tables in the form Tbl & the [FieldName] so Field 'BHP' Becomes TblBHP consisting of the following fields

User generated image
I dont know how to do that in VBA ive looked at the microsoft site which I havnt shown here as I might break the EE rules so here is a rough draft of something.

I am unsure of the syntax but, I am guessing you see my intention, even if I got the syntax or variable ref wrong.

Sub CreateTable()


     Dim db As Database
 Set db = CurrentDb
  
   
 For Each fld In db.TableDefs!(CAPDATA).Fields
    db.Execute "CREATE TABLE 'Tbl' & fld.Name " _
    & "(ClientCode CHAR," _
    & " ChangeYearMonth CHAR, " _
    & "ActualVariance dblong, " _
    & "VehicleCategory CHAR, " _
    & "Matched YESNO, " & fld.Name & "Prev CHAR," & fld.Name & "Change CHAR " _
    & "PKCodeChangeYearMonthField CHAR" _
    & "PRIMARY KEY);"
    
 Next
 
    

    db.Close
 
End Sub

Open in new window

Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Wait! You will have maintenance problem.
Instead, Create one table like:
fieldname, fieldType, oldValue, newValue, .... userID,

Store old and new values as text, and from field type you can reverse to the proper typed value.
For example, if field type is Integer, you save values as CStr(oldValue), and when reporting, use CInt(OldValue).
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

its the syntax of the code that i am unsure about, I have working table in another db but a new db has too many fields to create a table manually.
test this codes
Sub CreateTable()
 Dim db As DAO.Database, fld As DAO.Field
 Dim strTable As String, Sql As String
 Set db = CurrentDb
 For Each fld In db.TableDefs("CAPDATA").Fields
    strTable = "Tbl" & fld.Name
    Sql = "CREATE TABLE [" & strTable & "]"
    Sql = Sql & "( "
    Sql = Sql & "   ClientCode      TEXT, "
    Sql = Sql & "   ChangeYearMonth TEXT, "
    Sql = Sql & "   ActualVariance  LONG, "
    Sql = Sql & "   VehicleCategory TEXT, "
    Sql = Sql & "   Matched         YESNO, "
    Sql = Sql & "   " & fld.Name & "Prev TEXT, "
    Sql = Sql & "   " & fld.Name & "Change TEXT, "
    Sql = Sql & "   PKCodeChangeYearMonthField TEXT "
    Sql = Sql & "   PRIMARY KEY "
    Sql = Sql & ")"

    db.Execute Sql
    Application.RefreshDatabaseWindow
 Next
    db.Close
 
End Sub

Open in new window

<<I have a db with a data table and I want to create a table for each field to store data changes. >>

 If I'm understanding correctly, bad approach.  You'd want one 'logging' or 'audit' table like hnsar suggested.   ie:

http://allenbrowne.com/appaudit.html

although that uses a separate log table for each table.   No reason you can't do it all in one like hnsar suggested though.
Jim.
Although I prefer the one table log approach, I try to offer help as per your requirement.
new db has too many fields to create a table manually.
A database is supposed to have too many fields as our per requirement.
Agree you don't need to create the table manually.

Your code loops through the table fields, but the creation is manual.

To automate it, here is an example using a table u (f Long, t Text)
Set rs = CurrentDb.OpenRecordset("u")
    For Each fld In rs.Fields
        sql = "Create Table " & fld.Name & " (" _
            & "id Long" _                        'follow this manual field name and type example to add more shared field names.
            & ", " _
            & fld.Name & " " & TypeName(fld.value) _   'this is an automatic field name and type.
             & ")"
        CurrentDb.Execute sql
        Application.RefreshDatabaseWindow
    Next

Open in new window

I went with Ray's solution.. how do i deal with a field thats called "Body Type" access would allow me to create that manually i think but the auto creation produces error 3297 syntax error in field definition.

will adding [ field name] solve that?
I will stress there is only 1 field so not a big deal and as for the other comments I will be moving over too sql server in one table but still waiting for the company to get permission to buy which takes forever.
<will adding [ field name] solve that? > it should
post the code that you have
Sub CreateTable()
 Dim db As DAO.Database, fld As DAO.Field
 Dim strTable As String, Sql As String
 Set db = CurrentDb
 For Each fld In db.TableDefs("CAPDATA").Fields
    strTable = "Tbl" & "[" & fld.Name & "]"**********************ive added here but i get feeling it should be below as well
    Sql = "CREATE TABLE [" & strTable & "]"
    Sql = Sql & "( "
    Sql = Sql & "   ClientCode      TEXT, "
    Sql = Sql & "   ChangeYearMonth TEXT, "
    Sql = Sql & "   ActualVariance  LONG, "
    Sql = Sql & "   VehicleCategory TEXT, "
    Sql = Sql & "   Matched         YESNO, "
    Sql = Sql & "   " & fld.Name & "Prev TEXT, "********************************** here too?
    Sql = Sql & "   " & fld.Name & "Change TEXT, "  "********************************** here too?
    Sql = Sql & "   PKCodeChangeYearMonthField TEXT "
    Sql = Sql & "   PRIMARY KEY "
    Sql = Sql & ")"
    
    db.Execute Sql
    Application.RefreshDatabaseWindow
 Next
    db.Close

 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
we crossed this worked:

yours may be better

Sub CreateTable()
 Dim db As DAO.Database, fld As DAO.Field
 Dim strTable As String, Sql As String
 Set db = CurrentDb
 For Each fld In db.TableDefs("CAPDATA").Fields
    strTable = "Tbl" & fld.Name
    Sql = "CREATE TABLE [" & strTable & "]"
    Sql = Sql & "( "
    Sql = Sql & "   ClientCode      TEXT, "
    Sql = Sql & "   ChangeYearMonth TEXT, "
    Sql = Sql & "   ActualVariance  LONG, "
    Sql = Sql & "   VehicleCategory TEXT, "
    Sql = Sql & "   Matched         YESNO, "
    Sql = Sql & "   " & "[" & fld.Name & "Prev] TEXT, "
    Sql = Sql & "   " & "[" & fld.Name & "Change] TEXT, "
    Sql = Sql & "   PKCodeChangeYearMonthField TEXT "
    Sql = Sql & "   PRIMARY KEY "
    Sql = Sql & ")"
    Debug.Print Sql
    db.Execute Sql
    Application.RefreshDatabaseWindow
 Next
    db.Close

 
End Sub

Open in new window

i see its identicle just different placement
thank you