Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

Topic

New Reply

Hi all,

I have a couple of linked databases I use for work, and as part of a new project (for entertainment). I

Reply | Quote

Replies

Hi Tim,

Is there any reason you couldn’t write this functionality into a web application? This sounds like the obvious (although, not the easiest) solution.

This can be a big undertaking, depending on the complexity of information. However, if your data is relatively one-dimmensional, it should be rather easy.

Hope this helps!

Reply | Quote

Not a bad idea, but I’m only adding plants in the access app, not directly on the web page. (It’ll basically be an online catalog) Any other ideas? Thanks for your help!

Reply | Quote

Another approach might be to upsize you local copy to the MSDE, and then link your front-end to the MDSE tables with ODBC. Sort of a kludge, but it works in a pinch, and then you can use SQL Server replication, expecially if it’s a one-way update. That means of course that you need to learn lots about SQL Server however. Are you sure you need SQL Server? We run some web apps using Access when the updating is done locally, and we simply copy the .mdb file down and back up when we want to make updates. If lots of people are hitting the site, SQL Server would certainly be a better choice, but if it’s low traffic, Access works just fine.

On the other hand, Mark’s suggestion bears investigation. Doing database that update with web pages isn’t that hard, especially if you are using .NET.

Reply | Quote

Not a bad idea, but I don’t have access to the SQL Server replication service, I’m only running the SQL engine locally that comes with the .NET framework. I’d like to think the web site will be so popular I’ll need SQL Server, but really…. I can’t really link the tables with MSDE, as I’m working both at home and at work. Thanks for your help, and I’m all ears for more suggestions. It all comes down to the fact I don’t type well, and anything to ease data input is just wonderful.

Tim

Reply | Quote

If you are running the SQL Server developer version that comes with the .NET framework, then you can certainly link to it with ODBC data source connections, and administer it with local Access forms and so on. The fundamental question is where the .NET version of your database is going to be hosted. Are you running an Internet Server at home, or are you using an ISP service?

Reply | Quote

This might be a bit kludgy, but….

You’d need to create:
(1) A NewPlants table in your Access db
(2) A stored procedure in your SQL db that accepts input parameters of Plant ID, Plant Name, Plant Address and so forth. This stored proc then appends the record to the SQL Server Plants table.
(3) VBA code that (a) connects to the SQL db, ( loops through the Access NewPlants table and for each record it finds passes the necessary info to the stored proc © closes the connection upon completion, (d) deletes records from the NewPlants Access table.

As you add new plants to your Access Plants table, they also get written to the NewPlants table. You don’t have broadband, so when you have some free time on your dial up connection, you launch the process that calls the VBA you wrote for step 3. Hmmm…did I say a bit kludgy?!

I think you’ll be better off following the path that Mark and Wendell are outlining for you. Also, I’d suggest that if Office Developer is out of the ball park, financially speaking, licensing SQL Server for a production commercial application is, like, waaaaaay out of the park! By the way, MSDE, the SQL Lite that ships with .NET, isn’t allowed for production databases, and caps use to only 5 concurrent connections.

Good luck!

Reply | Quote