March 13, 2014 at 10:07 am
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
March 13, 2014 at 10:48 am
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!
March 13, 2014 at 11:23 am
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.
March 13, 2014 at 12:27 pm
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!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply