Question about collecting Access data into SQL Server

[Mika M]

Hi!

I have made quite simple Windows Forms application using VB.NET 2003. This
application in workstations only collect data into local Access database
table (4 tables) for manufacturing tracing purposes. Access DB is in
workstations locally as a kind of buffer because network is not available
all the time. I need to collect this data of the workstations into main SQL
Server database in certain time intervals when network connections are
available. SQL Server database contains those similar kind of tables where I
need to import access data. Any succestions how to do this? I think this has
been asked many times earlier - maybe someone will give me tip, or web-link,
or sample code ...

 

[William Ryan eMVP]

This will show you a quick easy way to transfer data in ADO.nET
http://www.knowdotnet.com/articles/datasetmerge.html However ADO.NET isn't
primarily designed to be a transfer mechanism in the same sense as say DTS.
This may be a better way. Or you may want to look into writing some local
web services, which you can poll and if connectivity is available, submit
the data from there.

Note that unless you are dealing witha lot of data, you don't need access.
DataSets act as local databases and datatables act the same way. Unless you
specifically need something Access does other than persistance, you are
adding an unnecessary layer in here which increases the likelihood of
something going wrong and makes things more closely coupled with Access. You
can just use DataSets and WriteXML or .ReadXML to accomplish most tasks
unless you are really dealing with a lot of data. From the sounds of it, it
sounds like you are using Access to do what a DataSet should be doing.

Either way though, if you coudl explain a little mroe about the app's goal
and how Access fits in, I could be of more help.

HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/