Function CreateTableInExternalDb(ByVal ExternalDBName As String, ByVal TableName As String, Optional ByVal CreateDB As Boolean) As Long
Const c_SQL = "CREATE TABLE @T ( Field0 COUNTER(1,1) CONSTRAINT PK_@T PRIMARY KEY, Field1 TEXT(50), Field2 LONG );"
Dim dbs As DAO.Database
On Error GoTo Err_CreateTableInExternalDb
If Len(Dir(ExternalDBName)) = 0 Then
If CreateDB = True Then
' Create a new database.
DBEngine.CreateDatabase ExternalDBName, dbLangGeneral
' File not found error code.
CreateTableInExternalDb = 53
If CreateTableInExternalDb = 0 Then
Set dbs = DBEngine.OpenDatabase(ExternalDBName, True)
dbs.Execute Replace(c_SQL, "@T", TableName), dbFailOnError
If Not dbs Is Nothing Then
Set dbs = Nothing
CreateTableInExternalDb = Err.Number
You really should not be creating one table for each job!
You need to stop, go back to basics and rethink your design.
Trust me when I say you are about to open a huge can of worms if you decide to go the way you are going.
Why do you want to create a new table for each job? What benefit do you see in this?
I am logging inventory charged to each jobnumber.
At the end of each week my shop foreman does transfers. Basically takes everything that has been been charged to a job number and currently produces a report from Lotus. The Lotus file is simply 5 or 6 columns. To name a few: "[material]", "[Unit Price]", "[Date verified]", "[Quantity]" etc. When quantity is updated a report can be printed. I have recreated the Lotus file in excel 2007 to bring it around to this decade. I began to get more and more ideas of what i could do with this data. Hence the reason for db's.
I have created a database called Inventory. This contains a few tables, one called "inventory" w/ fields "[material]", "[CategoryID]", "[SupplierID]", etc. Relationships have been created and everthing is going well so far. Now what im trying to do is create a way to use Access to create the report above. When a quantity is entered I need the row of information copied into the report. I want to keep a running talley on this list of information for future reports. I definitley could be going about this the wrong way. Hope that all makes sense. Suggestions are welcomed and appreciated.