Multiple Table Creation in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have got around 300 tables to be created in Access database using 300 SQL
scripts. I want to do it at one go in Access. Is there any tool or procedure
available to do this. As I know in Access using Queries I can create one
table at one time. But, my concern is how to create multiple tables (300
tables) at one go.
 
How about a table, with a primary key to indicate the order, and a memo
field to hold the SQL statements?

You could then OpenRecordset(), and Execute each statment in turn.
 
Allen,
Could you provide the macro or VBA assuming that the SQL statements are
stores in a table with a key to indicate order and a memo. Want a complete
solution, as I am not a programmer.
Thank you!
 
Hi GK

Our aim in these groups is to help you to find a solution for yourself,
rather than to do it for you.

If you have never written VBA code, you will have some learning to do.
If you do, looking up OpenRecordset and Execute will help.
 
Allen,
I have written a small VBA as below but I get this error.
Run-time error 91
Object variable or with block variable not set
at-- SqlQuery.SQL = Qval


Dim rs As DAO.Recordset
Dim SqlQuery As QueryDef
Dim Qval As String
Dim db As Database


Set db = CurrentDb
Set db = OpenDatabase("c:\Database.mdb")
strSQL = "SELECT * FROM GK_Tables"
Set rs = db.OpenRecordset(strSQL)
If rs.EOF = False Then
Do Until rs.EOF = True
Qval = rs.Fields("TableName").Value & ";"
SqlQuery.SQL = Qval
SqlQuery.Execute
rs.MoveNext
Loop
Else
MsgBox "no records!"
End If
Set rs = Nothing
Set db = Nothing
 
That's basically the idea.

We are assuming that the field TableName in BK_Tables contains a DDL SQL
statement such as this:

CREATE TABLE MyTable
(MyID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY,
Surname TEXT(30),
FirstName TEXT(20),
Inactive YESNO,
HourlyFee CURRENCY,
PenaltyRate DOUBLE,
BirthDate DATE,
Notes MEMO,
CONSTRAINT FullName UNIQUE (Surname, FirstName));


Your code can be simplifed a little:
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
strSQL = "SELECT * FROM GK_Tables;"
Set rs = db.OpenRecordset(strSQL)

Do Until rs.EOF
strSQL = rs.Fields("TableName").Value & ";"
db.Execute strSQL, dbFailOnError
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing
 
Back
Top