Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: Create table when job number is created

    So my company already has a job number database set up. When a job number is created its saved to a table in that DB. I want to create a DB that houses tables created for each job number.

    For example when a Job number is added to the jobs-PO.mdb, "jobs", "[jid]" create a table in job transfer.mdb named with that job number.

    There's no need to open this DB at this time, but it needs to be created and saved.

    Anyone know how I can achieve this.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Her's an example you can adapt to your needs:
    Code:
    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 
            Else
    
                ' File not found error code.
                '
                CreateTableInExternalDb = 53  
            End If
        End If
        If CreateTableInExternalDb = 0 Then
            Set dbs = DBEngine.OpenDatabase(ExternalDBName, True)
            dbs.Execute Replace(c_SQL, "@T", TableName), dbFailOnError
        End If
    Exit_CreateTableInExternalDb:
        If Not dbs Is Nothing Then
            dbs.Close
            Set dbs = Nothing
        End If
        Exit Function
        
    Err_CreateTableInExternalDb:
        CreateTableInExternalDb = Err.Number
        Err.Clear
        Resume Exit_CreateTableInExternalDb
                
    End Function
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    68
    Thanks for the code! I'm fairlynew at Access, I'm kinda teaching myself. I'm going to try to dive into the code and modify it to my needs.........i justhave no clue where to start.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by gvee View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •