I have an existing Access data base with a lot of legacy code, and I recently posted asking for a "simple" way to move to SQL Server. I was advised about the Access Upsizing Wizard which sounded terrific. However when I tried to use it, I ran into a connection error. I would appreciate your help -- it is probably some step I missed. The following describes what I have observed:
I downloaded SQL Server Express, then took one of the data bases (XFN) and trimmed out most of the records to use the data base as a test case, moving the lobotomized data base to another location. I then opened the data base using Access and selected Tools -> Database Utilities -> Upsizing Wizard.
When the Upsizing Wizard started I selected to Create a new database, use (local) SQL Server, and Use Trusted Connection, with default name for the new database (XFNSQL).
The Wizard then took a few seconds, followed by a popup with the error message:
SQL State: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory] Connection Open (Connect())
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory] SQL Server does not exist or access denied.
I suspected that there was some step in the download process that I missed, so I verified that the download had put Microsoft SQL Server 2005 in my Programs list. When I checked that program, I see that it has Configuration Tools as the only subset. Within Configuration Tools there are three items:
SQL Server Configuration Manager
SQL Server Error and Usage Reporting
SQL Server Surface Area Configuration.
When I select to start SQL Server Configuration Manager I get a window with two panes. The left pane has SQL Server Configuration Manager (local) on top with subcategories: SQL Server 2005 Services
SQL Server 2005 Network Configuration
SQL Native Client Configuration.
If SQL Server 2005 Services is highlighted the right pane shows
SQL Server (SQLEXPRESS)
with State= Running
Log On as=NT Authority
Process ID with Service Type=SQL Server;
also SQL Server Browser
Start Mode= Automatic
Log On as=NT Authority
Process ID with Service Type=SQL Server Browser.
SQL Server 2005 Network Configuration has subcategory Protocols for SQLEXPRESS. When this is highlighted the right panel shows
Shared Memory Enabled
Named Pipes Disabled
SQL Native Client Configuration has subcategories
When Client Protocols is highlighted the right panel shows
Shared Memory -> Order=1, Enabled
TCP/IP -> Order=2, Enabled
Named Pipes -> Order=3, Enabled
There are no Aliases.