SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access 2010 and SQL Server 2012 Upsizing Wizard


Access 2010 and SQL Server 2012 Upsizing Wizard

Author
Message
briancampbellmcad
briancampbellmcad
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 438
I'm using Access 2010 and SQL Server 2012. I have about 12 tables that are growing in size and need to be upsized to SQL Server - using the Upsizing Wizard I'm reconning to be the best approach. My DBA has created an empty database but I'm having trouble with the steps. I chose "Use Existing Database" and went on to "Machine Datasource" from which I chose "New" and "System Datasource" na dused the "SQL Server Native Client 11.0"... then it asks me to name it and provide a server name... I'm confused as I thought you were not to use a physical server name on the network but rather a SQL Server Instance... either way the server I'm to use seems nowhere to be found.... any ideas? Thanks!

BTW... I can create a table in my SQL SErver DB and view the script if that gives any clues:

USE[DTCBrec]
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATETABLE[ASDHBITrans\TiLinton].[tmpBank](
[Amount][decimal](28, 2)NULL,
[Store][int]NULL,
[TransactionDate][date]NULL,
[ConcatenateBank][nchar](20)NULL
)
GO
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 1831
I presume what you are doing is creating an ODBC Data Source from Control Panel - such connections are often called DSNs just to confuse the issue. You should start by creating a User DSN, not the System one - if you are running Win7 or later. You do specify a database name, which can be the same as or different from the SQL Server database name you want to connect to. You then use the drop down combo box to specify the SQL Server system and instance. Dropping it down does a browse to find all the SQL Server instances it can, and should produce a list as long as you can connect to one or more. Chose the one you want and then step through what security you are going to use and select the actual SQL Server database if it isn't your default, and finish by testing whether you can connect. Once that is done, you should be able to get the upsizing wizard to work. You can also upsize an Access database from SQL Server - the general concensus is that the SQL Server tool works better, but I personally don't see much difference.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
briancampbellmcad
briancampbellmcad
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 438
Actually I'm trying to use the Upsizing Wizard in Access instead of the ODBC tool (the latter of which I can get to through the control panel or Access itself has a tool built in)... I already have the tables in Access and just need to recreate them (and thus move their contents and structure) to SQL Server. The beauty of the Upsizing Wizard is touted that it simply moves the table from Access to SQL Server and all functionality remains - one can write to the SQL tables and read from them.

Seems that if all I needed was to use the ODBC tool it would assume that there is data in the SQL Server table to start with, which is not the case. All my data is in Access.
WendellB
WendellB
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 1831
Unfortunately, that's not the case based on my experience. You do need an ODBC datasource if you are going to link to the SQL Server tables, which is one of options in the upsizing wizard. If you choose to create a new SQL Server database, it will create and ODBC data source for you, but if you have an existing database, then you have to select an existing ODBC data source or create a new one. You can also create an ADP version of Access, which does connect directly to SQL Server tables, but requires a good deal of work on forms, reports and queries to become functional, and has been deprecated in the lastest version of Access.

The upsizing wizard does a pretty decent job of moving data to SQL Server in my experience, as it preserves data types in most cases (but does create nvarchar text fields in SQL Server which take twice as much space), and preserves default values, indexes and primary keys for the tables, as well as creating constraints that enforce referential integrity.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search