July 6, 2002 at 10:49 am
I have an access 97 database system which I want to take data from an html web site form submitted by visitors to our web site, and automatically insert this in to the database on our own network. We have a constant internet connection.
I am new to SQL Server and was wondering is there a way to do this using it?
Any help or ideas would be appreciated.
Thanks
David.
July 6, 2002 at 1:28 pm
One way to do it since you have a constant connection is to just insert it directly into SQL. You can do this easily by linking to the SQL tables. When Access creates the link it will look like "dbo_tablename", dbo being the sql owner. First rename your existing tables by adding "_old" or whatever, then remove the "dbo_" from the linked tables. All your existing code should work. This is not the best way, but its often good enough.
If you don't want to change the way you're storing data initially in Access, your choices are a batch operation or replication. For the batch you can either have code that executes periodically from Access via a timer or a SQL job. Whichever you choose, you'd have to be able to identify all the added records so you could do an insert, all the changed records so you could do an update, all the deleteds so you could do the deletes. Probably have to set one or more flags on the table so you can keep track. If you go with replication I "think" you can replicate from Access to SQL, I know for sure you can set up merge replication between the two. For the sake of completeness I'll mention that you could also use DTS to execute the batch operation. Of your options, replication is the best but far from easy to set up. Until you're really comfortable with SQL, I'd suggest either writing the data to SQL directly or doing the batch method (which is really faux replication).
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply