• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:


I have a Table in database 1 that I  need to transfer to database 2(identical table).  

Here is the code I have so far!

and this is the line I need help with" "combined" ="

Option Compare Database
Option Explicit

Private Sub cmdDBPassword_Click()

End Sub
Sub OpenAccess()

    Dim LPath As String
    Dim LCategoryID As Long
    Const DBPWD = "OPPS"

    'Path to Access database
    LPath = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"

    'Open Access and make visible
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True

    'Open Access database as defined by LPath variable and , false, dbpwd for password
    oApp.OpenCurrentDatabase LPath, False, DBPWD

End Sub

Sub ADOFrromAccessToAccess()
' exports data from the active Access database to another Access Database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
    ' connect to the Access database
    Set cn = New ADODB.Connection
  'Workgroup Security
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb;Jet OLEDB:Database Password=OPPS;"
cn.Properties("Jet OLEDB:System database") = "C:\Program Files\Microsoft Office\system.mdw"

    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Combined", cn, adOpenKeyset, adLockOptimistic, adCmdTable
      cn.Execute "delete * from Combined"
          docmd.Sendtable "combined" =
End Sub

1 Solution
Before I dig through all of your code, I have to ask if you knew about the docmd.copyobject function that would let you copy a table to a different database, as

docmd.copyobject "c:\mydir\database2.mbd","NewTableName", acTable, "OrigTableName"
ca1358Author Commented:
Thank you!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now