How to push data from local ADP to remote SQL server? christopher.b.lewis[ at ]gmail.com 03.08.2006 16:21:25 I have been using Access for many years. I now need to upsize to SQL because my website is getting much more traffic than it used to. I want to continue using my existing Access forms to populate the new SQL database. I have tried to link my existing local Access forms to my remote SQL sever database. However, my forms have many lookups that pull thousands of rows of data; so performance is inadequate. I was thinking that a quick and good solution would be to update my data locally using an Access Database Project (the lookups should perform reasonably well locally) and then push the new data updates to the live, remote SQL database. Does this sound like a fine way to work? Any suggestions on how I should push my new data to the remote SQL server? (I have never done this before.) What concepts/processes should I learn about to get this done? Any specific recommendations on how to begin would be much appreciated. One of my concerns is that during the data transfer from local to remote, the integrity of the relationships will break. Is there a standard way of dealing with that?

Re: How to push data from local ADP to remote SQL server? "Norman Yuan" <NotReal[ at ]NotReal.not> 03.08.2006 18:10:52 ADP does not contain data. It is pure frnt end application, connected to SQL Server, with all data sit in SQL Server database (while *.mdb front end can hold data on its local table). Using ADP in your situation does not help at all, or not much different from using *.mdb linked tables. <christopher.b.lewis[ at ]gmail.com> wrote in message news:1154622085.921249.242150[ at ]75g2000cwc.googlegroups.com... [Quoted Text] >I have been using Access for many years. I now need to upsize to SQL > because my website is getting much more traffic than it used to. I > want to continue using my existing Access forms to populate the new SQL > database. > > I have tried to link my existing local Access forms to my remote SQL > sever database. However, my forms have many lookups that pull > thousands of rows of data; so performance is inadequate. > > I was thinking that a quick and good solution would be to update my > data locally using an Access Database Project (the lookups should > perform reasonably well locally) and then push the new data updates to > the live, remote SQL database. > > Does this sound like a fine way to work? > Any suggestions on how I should push my new data to the remote SQL > server? (I have never done this before.) What concepts/processes > should I learn about to get this done? Any specific recommendations on > how to begin would be much appreciated. > > One of my concerns is that during the data transfer from local to > remote, the integrity of the relationships will break. Is there a > standard way of dealing with that? >

Re: How to push data from local ADP to remote SQL server? christopher.b.lewis[ at ]gmail.com 03.08.2006 19:17:49 [Quoted Text] > Using ADP in your situation does not help at > all, or not much different from using *.mdb linked tables. The data on my remote server needs to be in moved to SQL, but I want to maintain it locally with my existing Access forms and then push the updates to the remote SQL server. What do you suggest?

Re: How to push data from local ADP to remote SQL server? "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 03.08.2006 22:12:02 You can use Replication (difficult to set up when used over the internet), develop your own synchronising mecanism or use one of the many data synchronising tools that are on the market. Personnally, I like to use the tool from Red Gate. All these tools will use transactions for updating the data, so you don't have to have concerns about the integrity of your data, including relationships. The exception to this will be the case where the data on the website have also been edited (ie., in the case that you will need bi-directional synchronising) but this doesn't appears to be your case from your description. However, if you need it, the Merge Replication will easily (hum-hum...) overcome this problem. Other possibilities would be to use ADP to update the data directly on the remote SQL-Server. When well designed, an ADP solution will be faster than an MDB solution. The idea of using, if necessary, a local SQL-Server for fast retrieving of lookup data might seems a good idea but it will not be easy to do with ADP because of the lack of a direct support for multiple connections. (But it can be done if you want to). Finally, using other solutions like Terminal Server, ASP.NET or WinForms will give you the best performance for a remote access. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF <christopher.b.lewis[ at ]gmail.com> wrote in message news:1154622085.921249.242150[ at ]75g2000cwc.googlegroups.com... [Quoted Text] >I have been using Access for many years. I now need to upsize to SQL > because my website is getting much more traffic than it used to. I > want to continue using my existing Access forms to populate the new SQL > database. > > I have tried to link my existing local Access forms to my remote SQL > sever database. However, my forms have many lookups that pull > thousands of rows of data; so performance is inadequate. > > I was thinking that a quick and good solution would be to update my > data locally using an Access Database Project (the lookups should > perform reasonably well locally) and then push the new data updates to > the live, remote SQL database. > > Does this sound like a fine way to work? > Any suggestions on how I should push my new data to the remote SQL > server? (I have never done this before.) What concepts/processes > should I learn about to get this done? Any specific recommendations on > how to begin would be much appreciated. > > One of my concerns is that during the data transfer from local to > remote, the integrity of the relationships will break. Is there a > standard way of dealing with that? >

Re: How to push data from local ADP to remote SQL server? christopher.b.lewis[ at ]gmail.com 09.08.2006 20:31:21 Thanks for the advice!