Update Databse from another Db

SQL is not being used. it is 2 Access Databases

Okay then... write a query on the master database, and use it to populate the dataset.

Do the users always have a connection to the master database?

For MS Access
=============

execute the following SQL

INSERT INTO Table1 IN 'ExternalDatabasePath1' SELECT *
FROM Table1 IN 'ExternalDatabasePath2';
where externaldatabasepath1 & 2 are something like c:\databases\db1.mdb & f:\databases\db2.mdb
(INSERT INTO Table1 SELECT * FROM Table2  IN 'f:/aaa/bbb/Database.mdb')

No they do not always have a connection.  I wrote a function to go Online.  From there they have the option to upgrade.  I just need to know to upgrade.

what do you mean by query on the master Db.  I have created a query that I am using just to test.  It is inside the master db already. Can you call that query just like a stored procedure in SQl?


MJ

iboutchkine,

I like your approach -- wasn't sure it was possible from two different databases in Access. Can the external db path include an IP address?

malanois,

You can reference a query on the master database just like it were a table.  I know you can pass a parameter to the query as well -- you'll have to use the OLEdbCommand object and set it's parameters. Fortunately, I'm more accustomed to SQL, and so don't know the exact syntax to do this. IIRC, there's something quirky about the @ symbol in OLE.

Jeff

>> Can the external db path include an IP address
I am not sure. Just try it
//IP/path/fb

iboutchkine,

That would copy the whole database correct?
If so I cannot do that.  The DB is over 400MB now and is going to grow again here in the future.  That why I only wanted to send changes from the master to the loal DB since the last Update.

Nope... that's why there's a SELECT statement -- it only copies one table. I might add a WHERE statement, though...

INSERT INTO Table1 IN 'ExternalDatabasePath1'
(SELECT * FROM Table1 IN 'ExternalDatabasePath2' WHERE LastUpdate >= table.LastUpdate)

If your database is that large, should you be considering archiving some of the data to make the tables in actual use somewhat smaller?

It is regular SQL which can insert the whole or the part of the table ( you can add where clause)

is there a reason why you cannot LINK to the tables in the 'master db' - a LINK does NOT copy the table, simply provides a LINK to the external table.

AW

Arthur Wood
Can You explain more on that.

Chaosian, the whold databse is being used.  It's apart Database with over 500,000 parts in it.  The different tables have different information that are linked to that main Part Table.  one table is image paths, another is web site links.  

But once again the main table has links thorught the different tables inside the DB.  I want run it from there.

Like this
                     V-----------------------I think I can use this instead of typing all the tables
Select * from * where MainPart.EntryAdded_date >= (Local DB)UserSettings.LastUpdate_Date

Let me know if the SELECT * FROM * works for you... I'm skeptical.

If you have 500K entries, you really need to think about moving to SQL...

Go to the tables tab. Right-click. Select link to a table. This essentially creates an ODBC connection string behind the scenes, and allows users to view the data from the master database.  You can link to SQL, Oracle, etc -- anything that accepts ODBC connections.

This approach will be SLOW... make sure that all your queries reside on the master database and are linked in a similar fashion -- otherwise, you'll bring ALL the data across your connection... then the query will be run.

Is there anyway to show if/how many rows were updated by doing the complete table to table.

MJ

Using the OLEDbCommand object would allow you to une ExecuteNonQuery, which returns the number of rows affected. I'm not sure if there's an easy way to do this in the direct approach outlined above. However... you know that you're only updating records that have the lastUpdate date >= some value. You could just run a quick query against the table to find the count of the records that meet his criteria.

Yes, that what I want, just with the >lastUpdate.  

This is what I have done.  I'm not getting an error, yet it is not giving me my message box.


        Dim sConn As New OleDbConnection
        sConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "X:\T\T.mdb" & ";Persist Security Info=False"

        Dim sConn1 As New OleDbConnection
        sConn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "C:\Program Files\T\T.mdb" & ";Persist Security Info=False"


        Dim CmdTxt = "Update T_PARTS IN 'C:\Program Files\T\T.mdb' SELECT * FROM T_PARTS IN 'X:\T\T.mdb' "
        '(SELECT * FROM Table1 IN 'ExternalDatabasePath2' WHERE LastUpdate >= table.LastUpdate)"
        Dim Cmd As OleDbCommand = New OleDbCommand(CmdTxt, sConn1)


        'Open The Database
        sConn.Open()
        sConn1.Open()
        Try
           
            'Update the row
            Cmd.ExecuteNonQuery()
            'Now close everything
            Cmd.Dispose()
            sConn.Close()
            MessageBox.Show("success")
            Exit Try
       
        Catch ex As Exception
            MsgBox(ex.ToString())
            sConn.Close()
            sConn1.Close()

        End Try

I don't see where you're using sConn at all.

Try replacing the above code with this (please excuse the indentation):

Dim sConn1 As New OleDbConnection
        sConn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "C:\Program Files\T\T.mdb" & ";Persist Security Info=False"
       Try
        sConn1.Open()
Dim CmdTxt = "Update T_PARTS IN 'C:\Program Files\T\T.mdb' (SELECT * FROM T_PARTS IN 'X:\T\T.mdb' " & _
"WHERE LastUpdate >= table.LastUpdate))"

cdm =  New OleDbCommand(CmdTxt, sConn1)
            'Update the row
            Cmd.ExecuteNonQuery()
            MessageBox.Show("success")
        Catch ex As Exception
             MessageBox.Show(ex.message)
Finally
 'Now close everything
            Cmd.Dispose()
            sConn1.Close()
        End Try

typo: "WHERE LastUpdate >= table.LastUpdate))" should be ""WHERE LastUpdate >= table.LastUpdate)""

"" Exception has been thrown by the target of an invocation"

I copied the above code over directly,  I changed 1 thing to narrow the search even more

 Dim CmdTxt = "Update T_PARTS IN 'C:\Program Files\T\T.mdb' (SELECT * FROM T_PARTS IN 'X:\T\T.mdb' WHERE LastUpdate >= 7/1/2004)"

I have tried it as this as well
Dim CmdTxt = "Update T_PARTS IN 'C:\Program Files\T\T.mdb' (SELECT * FROM T_PARTS IN 'X:\T\T.mdb' )"
and I get the dame error

MJ

7/1/2004 needs to be # 7/1/2004# to let Access know you're dealing with a date.

Dim CmdTxt asstring = ....

and


cmd =  New OleDbCommand(CmdTxt, sConn1)

I already took care of the variables.

I took out the where clause and I still get the rror


MJ

I have tried the update cmd directly in Access and get a syntax error


Update PARTS IN 'C:\Program Files\T\T.mdb' (Select * FROM PARTS IN 'X:\T\T.mdb')