SQL Server to Access Question

[drtree]

Hi,


I have an Access database that serves as the database for a website. It is an Online Community type website, where users can update their information, search for other users, etc. Each morning, a data entry person will come in, run some reports (in Access) and get a list of all the changes that were made in the last day to the Community database. This data will then be entered into a SQL Server database. I am supposed to set up a nightly process that exports data from SQL Server to this Access database. I have the export process setup (using DTS) but just have one concern - what if the data entry person is out? My concern is that someone will log on, update their data, and then log out. This data, since the entry person is out, will not get put in the SQL Server system. If someone else went into the SQL Server database and made an edit to the same person’s record, the information in the SQL Server database could be used to update the information in the Access database (possibly losing all the updates the person made). I was thinking about creating another DTS package and retrieving any changed data and placing it in SQL Server - in essence creating a set of change history tables. My question is this - is it worth it? Is there a better way to do this?

Thanks,

Dr Tree

 

[mrdenny]

My not just have the Web Site run off the SQL Server database, and ditch the access database all together. This will resolve this problem, get rid of the DTS packages, and the need for the data entry person to come in daily.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[drtree]

Denny,

Thanks for the reply. Unfortunately, someone else (who is higher up on the chain) controls this and has decided that he wants to use Access, so that's the way it's going to be. I had hoped he'd go with SQL Server as it would've made my life much easier as well. So, with that in mind, do you think this is the best solution?


Thanks again,

Doc Tree

 

[mrdenny]

In that case, I would setup a DTS package that would export the data from the access database, and load it into the SQL Server. It could then massage the data as needed, and load the updated data back to the Access database when it's done.

This way it's entirely automated, doesn't require a person to be in the middle, and you can save of the cost of the persons sallary (or find them something better to do).

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[BikeBoy20032004]

mrdenny is right, t-sql and ado is much more powerful than access macros, and you're better off doing all your data manipulation on the server side.