Update Databse from another Db

I currently wrote an app that is located on the individual PC's.  I want to update the Access DB that is stored locally from a Main Database that is on a Networked shared server.

The databse has 10 tables in it.  I have created a field for the local database that is table.LastUpdate
The server database has a field for every row that is table.partupdated.

So when I run the query instead on updating 400 MB's of data that it only updates >= table.LastUpdate

I am having problems with the way to create the transfer of the data.  i dont think I can just pass the dataset returned from server to local.  i believe I have to use some sort of streamreader or datarow.

The second part is if a row doesnt exist in local.db I would have to use an insert into not update command correct?

Or is there a way to accomplish both at the same time?

Any suggestion to point me in the right direction would be great.

Thanks for the Help.

MJ - Little Experience but I love to learn.
malanoisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeff CertainCommented:
Start by creating a stored procedure on the server to return the data you want. Open a connection to the SQL Server. Call the stored procedure to retrieve all the new data as a dataset or datatable. Close the connection.

Then, step through the data source to write each record to the Access database using INSERT or UPDATE. There are two ways you can handle the issue of existing data:
1. Write a query to determine if the data exists, then either INSERT or UPDATE depending on the results of this query.
2. Try (using Try..Catch) to INSERT and if it fails because of a duplicate record, then perform the UPDATE.

If you need the code to access a stored procedure from VB.NET, let me know.

Jeff
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
malanoisAuthor Commented:
SQL is not being used. it is 2 Access Databases
0
Jeff CertainCommented:
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?

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

iboutchkineCommented:
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')
0
malanoisAuthor Commented:
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
0
Jeff CertainCommented:
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
0
iboutchkineCommented:
>> Can the external db path include an IP address
I am not sure. Just try it
//IP/path/fb
0
malanoisAuthor Commented:
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.

0
Jeff CertainCommented:
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)
0
Jeff CertainCommented:
If your database is that large, should you be considering archiving some of the data to make the tables in actual use somewhat smaller?
0
iboutchkineCommented:
It is regular SQL which can insert the whole or the part of the table ( you can add where clause)
0
Arthur_WoodCommented:
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
0
malanoisAuthor Commented:
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
0
Jeff CertainCommented:
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...
0
Jeff CertainCommented:
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.
0
malanoisAuthor Commented:
Is there anyway to show if/how many rows were updated by doing the complete table to table.

MJ
0
Jeff CertainCommented:
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.
0
malanoisAuthor Commented:
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
0
Jeff CertainCommented:
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
0
Jeff CertainCommented:
typo: "WHERE LastUpdate >= table.LastUpdate))" should be ""WHERE LastUpdate >= table.LastUpdate)""
0
malanoisAuthor Commented:
"" 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
0
Jeff CertainCommented:
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)
0
malanoisAuthor Commented:
I already took care of the variables.

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


MJ
0
malanoisAuthor Commented:
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')
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.