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
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.
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
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
<<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.
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.
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)
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
ASKER
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?
will adding [ field name] solve that?
ASKER
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
post the code that you have
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
we crossed this worked:
yours may be better
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
ASKER
i see its identicle just different placement
ASKER
thank you
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).