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

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.
0
malanois
Asked:
malanois
  • 11
  • 9
  • 3
  • +1
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now