Access VBA code to create a table from one database to another database (1 Viewer)

[mansied]

Hello
I am Looking for Access VBA code to create a table from one database X to another database Y which includes the date in name of the new table,.thank you

Function TransferTable()

Dim objAccess As Access.Application

Set objAccess = New Access.Application
'open the destination access database
Call objAccess.OpenCurrentDatabase("C:\*****.accdb")
'create table in destination db
objAccess.CurrentProject.Connection.Execute ("Create Table mytable." & Year(Date) & "." & MonthName(Month(Date)))
End Sub
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\*****.accdb", acTable, "source table", "destination table", False

End Function

 

[pbaldy]

You haven't said what the problem is, but I don't believe you can create a table with "." in the name.

 

[mansied]

the problem is in the name of the destination table in the second table.I need to add the year and month in the name of the new table.

 

[pbaldy]

Like I said, you can't have periods. Try underscores instead.

 

[mansied]

I did but it doesn't accept ??
Now I am getting a "Type Mismatch "error code.
Actually , I want to create a table and change its name with the current date.


Function CopyTable()
Dim objAccess As Access.Application
Set objAccess = New Access.Application
'open the destination access database
Call objAccess.OpenCurrentDatabase("Microsoft Access", "*****.accdb")
'create table in destination db
objAccess.CurrentProject.Connection.Execute ("Create Table myTable.& Year(Date) & " - " & MonthName(Month(Date))")
DoCmd.TransferDatabase acExport, "Microsoft Access", "******.accdb", acTable, "oldTable", "newTable." & Year(Date) & "-" & MonthName(Month(Date)), False

End Function

 

[pbaldy]

Your concatenation is off, and you still have a . in there. Try

objAccess.CurrentProject.Connection.Execute ("Create Table myTable_" & Year(Date) & " - " & MonthName(Month(Date))")

By the way, you aren't creating any fields, so I'm not sure how usable this table will be.

 

[pbaldy]

I recalled having answered a question about this type of thing before and found the working sample code (forum poster's code that I tweaked to work):

    Dim db As Database
    Dim DbPath As Variant
    Dim Td As dao.TableDef
    Dim TdName As Variant
    Dim FD As dao.Field

    DbPath = "C:\PaulFiles\Access databases\TimeTracker.accdb"
    TdName = "tblWhatever"

    'Initialize the table.
    Set db = OpenDatabase(DbPath)
    Set Td = db.CreateTableDef(TdName)

    'Specify the fields.
    With Td
        Set FD = .CreateField("TestTest", dbText, 15)
        .Fields.Append FD
        Set FD = .CreateField("AtticStockYN", dbBoolean)
        .Fields.Append FD
    End With
   
    'Save the table.
    db.TableDefs.Append Td
    Set FD = Nothing
    Set Td = Nothing
    Set db = Nothing

 

[arnelgp]

see this link for the proper syntax:

CREATE TABLE statement (Microsoft Access SQL)

Office developer client VBA reference documentation

docs.microsoft.com

 

[mansied]

Thanks but how do you copy data from the first database to the second one?
with your code seems we can open the second one and create a table and add fields, and now how copy data? and change the second table with the current date on it????

 

[Minty]

PMFJI But if the fields will be the same can't you use a

SELECT originaltable.* INTO...

SELECT.INTO statement (Microsoft Access SQL)

Office developer client VBA reference documentation

docs.microsoft.com