Need help exporting to SQL server

[usvijay]

I have a local Access database that gets rows inserted by a VB App that already exists.
With each row insertion into this local Access database i need to take that row and export to a Master SQL server database.
I didnt want batch export like a replication or DTS since i want to have each new row inserted exported instantly rather than as a batch process.
Can anyone help with some ideas???
Pls give me as many details as possible since iam a novice Access programmer
Thanks for your help.

 

[Pat Hartman]

You don't need to keep the data in the Access table at all. Just link to the SQL server tables and refer to those tables in your queries and recordsources for forms and reports.

 

[usvijay]

Thanks for your posting Pat.
However we want to have several local Access databases at several individual sites and all these local Access databases feed data to a central master SQL server database.
So we want to write to data to Access and we already have an App that does that.
Now we are trying to have some code or logic that would take the newly inserted row and send it to SQL server instantly.This happens at all local Access databases.
any ideas will be appreciated.

Thanks

 

[Travis]

In order to do this you would have to have an application that constintaly monitored how many records there are in the Access Database. (This is better suited for a Visual Basic or even a C or C++ application)

My first suggestion would be this:

If the VB app that populates the Access Application is not using "BOUND" data controls, and you are able to modify the existing VB app, have it populate to both places.

Or

Create your own VB/C/C++ App to monitor the local MDB.

I pray you can modify the current VB app.

 

[vishal]

Travis,
Please give me some ideas on this.
If i need to write a VB app that monitors Access table every 1 min or something and if a new row is inserted it should take it and send it to SQL server acress ODBC.
My question now is...how would we monitor for new rows?
please help with some ideas.

Thanks a lot for your help.

 

[Travis]

In the Access Database you will need to have a new field (perfered type Boolean [True=Has been sent False=Has not been Sent]). The VB app would then create a recordset based on all records that have not been sent. It would then append them to the SQL Server and update the Access Table Field to mark it as being sent.

While it is doing this you will need to keep the following in mind; You need to stop the timer until it is finished updating; You need to restart the timer when it is finished.

This method is a batch style since it is not possible (Access does not have a new record Event)

You will know if there are any records to update to the SQL Server by the Record Count in the Recordset (I would use ADO here).

But I still strongly maintain that it would be better to have the Original VB App populate both Access and SQL.