Updating table in one Access database from another?

Question

I have an Access 2007 database on my local PC, and another one out on the network.

local: c:\mydatabase.accdb network: \server\share\publicdatabase.accdb

Both databases have 2 identical tables, let's call them Table1 and Table2

My process involves exporting data from a PICK database to a delimited text file, then importing it into Access.

Currently, I update the tables in my local db, then in Access I copy/paste the table from my local db to the network db. I'm hoping to do this via VBA.

I found the following code that would be used on the network db to clear a table and then 'pull' the update, but I need to run it from my pc to clear the network db table and then 'push' the update.

Dim AccessConn As New   System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test Files\database.mdb")

AccessConn.Open()
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM [Catlog]",  AccessConn)
AccessCommand.ExecuteNonQuery()
AccessCommand.CommandText = "INSERT INTO [Table1] SELECT * FROM [MS  Access;DATABASE=C:\Test Files\database.mdb;].[Table1]"
AccessCommand.ExecuteNonQuery()
AccessConn.Close() 

Also if it's not too much trouble, how could I include a check to first make sure the network db is available to be updated? (not already open by other user)

Thank you!!

EDIT: this works so far:

With Access.DoCmd
    .RunSQL "Delete FROM Table1 IN '\\server\share\publicdatabase.accdb'"
    .RunSQL "Insert INTO Table1 IN '\\server\share\publicdatabase.accdb' SELECT * FROM Table1"
End With

Answer 1

Gord Thompson gave an excellent answer which help me implementing my use case. I had to copy SOURCEDB.tableXX rows to DESTDB.tableXX. Not all table data should be copied each time this function is run.

I created a new ACTIONSDB.accdb msaccess file where inserted a new vba Module1. This probably should work in Excel vba module as well. This is simplified version without my special-case when_not_to_copy_based_on_month_and_day rules.

Option Compare Database
Option Explicit

Sub importDataFromDB()
' Copy table rows from source MSAccess db to another db
On Error GoTo ErrorLabel
    Dim idx As Long
    Dim tables As Collection
    Dim sTable As String
    Dim sql As String
    Dim con As Object

    Set tables = New Collection
    Call tables.Add("tbl_data1")
    Call tables.Add("tbl_data2")
    Call tables.Add("tbl_data5")
    ' ...more tablenames

    Set con = CreateObject("ADODB.Connection")
    con.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
            "Dbq=C:\data\DESTDB.accdb;" & _
            "Exclusive=1;" & _
            "Uid=;" & _
            "Pwd=;"
    con.Open
    Debug.Print ""
    Debug.Print "Tables (" & tables.Count & ")"
    For idx = 1 To tables.Count
        sTable = tables.Item(idx)
        Debug.Print sTable & " (" & idx & "/" & tables.Count & ")"
        sql = "INSERT INTO ${table} SELECT * FROM [MS Access;DATABASE=C:\data\SOURCEDB.accdb;].[${table}]"
        sql = Replace(sql, "${table}", sTable, 1, -1, vbTextCompare)
        con.Execute "DELETE FROM " & sTable
        con.Execute sql
    Next idx
    con.Close
    Debug.Print "Completed"

ExitLabel:
    Exit Sub

ErrorLabel:
    Debug.Print Err.Description
    con.Close
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

Answer 2

Your solution appears to be sound. We're still in the dark ages at work (A2003) so I'm not sure about this; is there no locking file in A2007? If so, you can open it and make sure there are no entries (the same process you use to import that PICK file), and if it's clear then you can run your import/export process.

Answer 3

The following C# console application works for me. Notice that it uses ODBC and it opens "publicdatabase.accdb" with Exclusive=1; to ensure that nobody else is using it. I would hope that it wouldn't be too difficult to apply the same techniques to a VBA solution.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;

namespace odbcTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var con = new OdbcConnection(
                        "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
                        @"Dbq=C:\__tmp\publicdatabase.accdb;" +
                        "Exclusive=1;" +
                        "Uid=admin;" +
                        "Pwd=;"))
            {
                try
                {
                    con.Open();
                }
                catch
                {
                    Console.WriteLine("Exclusive 'Open' failed. Quitting.");
                    System.Threading.Thread.Sleep(2000);
                    return;
                }
                using (var cmd = new OdbcCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = "DELETE FROM Table1";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"INSERT INTO Table1 SELECT * FROM [MS Access;DATABASE=C:\__tmp\mydatabase.accdb;].[Table1]";
                    cmd.ExecuteNonQuery();
                }
                con.Close();
            }
            Console.WriteLine("Done.");
            System.Threading.Thread.Sleep(2000);
        }
    }
}

Edit

The corresponding VBA code would be something like this. It is intended to be run from the local database (mydatabase.accdb) and it uses Application.CurrentDb.Name to avoid hard-coding the local database path (in case "mydatabase.accdb" gets moved to another location):

Sub UpdatePublicDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdatePublicDatabase_OpenError
con.Open _
        "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
        "Dbq=C:\__tmp\publicdatabase.accdb;" & _
        "Exclusive=1;" & _
        "Uid=admin;" & _
        "Pwd=;"
On Error GoTo 0
con.Execute "DELETE FROM Table1"
con.Execute "INSERT INTO Table1 SELECT * FROM [MS Access;DATABASE=" & Application.CurrentDb.Name & ";].[Table1]"
con.Close
Debug.Print "Done."
Exit Sub

UpdatePublicDatabase_OpenError:
Debug.Print "Exclusive 'Open' failed. Quitting."
Exit Sub

End Sub