UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sync Access / SQL Server Tables, Access 2016    
 
   
puttputt
post Jun 8 2019, 03:06 PM
Post#1



Posts: 5
Joined: 6-April 19



I've spent considerable time rebuilding a database using SQL Server Express as the backend with the intent to ultimately move tables to a Corporate SQL Server. Despite initially being told by IT we could simply restore my test database from Express into the main server I'm now finding out that is not the case.

I am able to build queries in Access to "manually" move data between the two sources; however tables with Autonumber/Identity keys present a problem in that the key gets renumbered on the production server thereby loosing my record lookup reference. Additionally, at times I need to be able to work offline but be able to sync data back into the Corporate server.

I've read where SQL Server command Identity_Insert table1 On / Off allows migration of data into an Identity field but not sure that is the most efficient workaround.

Thanks in advance for your help.
Go to the top of the page
 
nvogel
post Jun 8 2019, 04:18 PM
Post#2



Posts: 950
Joined: 26-January 14
From: London, UK


What is the problem with using backup and restore? Is it perhaps that you have a later version of Express and therefore you need to downgrade? I suggest you start by downloading and installing SQL Server Developer Edition. Better not to develop and test using Express if you ultimately intend to use Standard or Enterprise Edition.

I think RESTORE and the Copy Database Wizard are not an option if you need to downgrade. However, SQL Server Management Studio can generate database creation scripts and data creation scripts for any version from 2005 - 2017, so that may be the best solution provided your data is not so large that scripting is impractical. Generate scripts instructions here.

For larger databases use BCP out and then BCP in. Use the BCP -E option to import your IDENTITY values. bcp Utility
This post has been edited by nvogel: Jun 8 2019, 04:22 PM
Go to the top of the page
 
GroverParkGeorge
post Jun 8 2019, 04:19 PM
Post#3


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Have you evaluated SQL Server Migration Assistant for this task?

It's a free download. It's intended for tasks exactly like this. Make sure you get the same bitness as your OFFICE installation.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
puttputt
post Jun 8 2019, 05:26 PM
Post#4



Posts: 5
Joined: 6-April 19



Thank you for the response(s).

The Restore feature on the Enterprise server does not allow import of external data. If the test DB was also on the server I could migrate data between test and production but that is not the case.

I'm trying to develop an approach through an ODBC connection so as to not rely on the IT group for assistance. They've given me "ownership" of a database on their server but access to SQL Server support features is somewhat limited.

I'll look into the SQL Server Migration Assistant approach but once again that will require IT's involvement to get the app loaded on my machine.

I appreciate your time and input.
Go to the top of the page
 
MadPiet
post Jun 8 2019, 05:32 PM
Post#5



Posts: 3,120
Joined: 27-February 09



"the Enterprise server does not allow import of external data"
... how are you trying to import the data? you can use SSIS to create an import routine and then have the package run under a Windows account that has permissions to import data.
Go to the top of the page
 
nvogel
post Jun 9 2019, 12:56 AM
Post#6



Posts: 950
Joined: 26-January 14
From: London, UK


QUOTE
The Restore feature on the Enterprise server does not allow import of external data.


Yes it does. You just need to copy your backup file to a location that is accessible from the server. Whoever supports the server should be able to help you with that.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 05:41 AM