Creating table with template (both in backend) only creates a link rather than table

[Seamus59]

I have a template in the backend database that I want to use to create another table in the backend, just with a specific name and it only creates a link in the backend to the template. The link has the new name.

DoCmd.TransferDatabase acExport, "Microsoft Access", BackEnd, acTable, "Template", strNewTableName , True

Previously, I created the new table in the front end, moved it to the back end with the statement above (except source table name = dest table name) which worked, then created the link. I'm trying to sreamline my code by just creating it in the backend, but it doesn't seem to be working.

Any ideas?

[June7]

Here's code from my db that creates table in backend.

Dim tdf As TableDef
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database

If IsNull(Me.tbxTestNum) Then
    MsgBox "Must enter test number.", vbCritical, "Error"
Else
    Set cn = New ADODB.Connection
    'connect to the backend database
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
    'create the test table
    cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
    'set table link
    Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
    tdf.SourceTableName = Me.tbxTestNum
    tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
    CurrentDb.TableDefs.Append tdf
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
    Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
    While Not rs.EOF
        If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
            'create field in new table
            cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
                        rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
                        IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
        End If
        If rs!DataType = "Text" Then
            'change the AllowZeroLength default Yes to No
            db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
        End If
        rs.MoveNext
    Wend
    
    rs.Close
    cn.Close
    db.Close
End If

[rpeare]

How are you defining backend... is that a string that you are creating elsewhere in your code?
what about your 'strnewtablename' I assume that's a constructed string to create a unique table name.

have you tried using debug.print for both of these to see what it's actually got for those two values.

I used this:


DoCmd.TransferDatabase acExport, "microsoft access", "C:\test\sample.accdb", acTable, "TestTable", "renamedTestTable", True

and it created an empty tab;e in the target database (c:\test\sample.accdb) with the same structure as the original table.

[Seamus59]

I have figured out that the source table must be in the front end. If the source table is in the back end, Access creates a LINK to it with the new tables name. If anyone knows of a work around, let me know!

[June7]

I am confused by what you want to do.

My code in post 2 creates a new table in the backend based on source table in the frontend then sets up link to the new table. Isn't that what you want?

Actually only used the procedure couple times in 6 years.

Are you having to regularly modify database with new tables? Sounds like a design flaw.

[Seamus59]

Thanks for your reply. I have an application where the user can create new "Projects". When the project is created, I use a table as a template to create a table with the new project information. All my tables have been located in the backend, but I've had to move the template into the front end. If the souce is in the back end, Access ceates a link. If in the front end, it works as expected. It was driving me crazy until I figured that out.

Thanks.

[June7]

A table for each project? WHY? Why does a new project require a separate table? Why not one table and just add records?

[Seamus59]

That describes my central table, but each project has multiple tables associated with it and many records in each table. To create these tables, I use templates to create the tables associated with a new project.

[June7]

Still don't understand why need to create new tables. Sounds like a non-normalized structure. I understand a non-normalized structure might be best for a situation (I have that) but I still rarely have to create new table. I have to create a new table if the lab decides to institute a new test procedure. Each test requires its own table for its data. Separate tables to make data entry input and output simple. We do very little statistical analysis, just report the numbers.