Problems getting MS Access to upsize to SQL Server EXpress
-
Hello,
I'm trying to work out how to upsize my MS Access database application to work with an SQL Server Express backend. I installed SQL Server Express and have then attempted to use the upsizing wizard in Access, but I don't get very far. Can anyone help me please.
In the SQL Server Configuration Manager I have the following services are listed:
Name | State | Start Mode | Log On As
------------------------+---------+-------------+----------------------------
SQL Server (SQLEXPRESS) | Running | Automatic | LocalSystem
SQL Server Browser | Running | Automatic | NT AUTHORITY\NetworkService
SQL Server MSSQLSERVER | Running | Automatic | NT AUTHORITY\NetworkService
... and in the SQL Server (SQLEXPRESS) properties the logon as: is set to 'Built-in account' - 'Local System'
With the MS Access Upsizing Wizard the sequence of steps is as follows:
Step 1:
I select the option - 'Create new database'
... and I click 'Next'
Step 2:
'What SQL Server ...' => 'SQLEXPRESS' (I have to type this, it isn't an option listed in the combo-box)
'Please specify login ID ...' => I tick 'Use trusted connection'
'What do you want to call it' ... => I type 'EGG' (an acronym for the name of my MS Access application)
... and I click 'Next'
I then get a dialog box titled "Microsoft SQL Server Login" with the following error messages:
Connection failed:
SQLState" '0100'
SQL Server Error: 53
[Microsoft]ODBC SQL Server Driver][DBNETLIB]COnnectionOpen (Connect())
Connection failed:
SQLState" '08001'
SQL Server Error: 17
[Microsoft]ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Does anyone have any thought on this please.
-
Hi,
This looks like a permission issue. Make sure that you have access to the server. You can check this by creating a test table with a primary field and some other test fields in the SQL server, go to MS Access, do an ODBC link to the test table, make sure you can open the link table, insert data to it.
-
I'm thinking that the problem may involve finding the correct name for the SQL Server instance. It would make it easier to troubleshoot if you can install the SQL Server Enterprise Manager client, which would probably show you what the instance was named. You didn't mention if any server names come up in the combo box - you might try using the computer name as the "Server Name", or you could create an empty .adp file with Access and perhaps it would show you the server name when you went to establish the connection.
Dick
-
Thanks Thuan and Dick,
It turns out that I have issues with my installation due to some earlier beta components that haven't uninstalled properly. These are preventing SQL Server or SQL Server Express from installing properly. I've been trying to work this out, but not having much luck. It's looking like I might have to format my drive an re-install everything from scratch ... uuuurrrgh.
Craig
-
Servername = ".\sqlexpress" (if local) or "[Fully Qualified Domain Names]\sqlexpress" (if network)
-
I've seen mucho material saying that the SQL upsizing wizard will not work with SQL Express, period. You're probably better off just rebuilding the tables using the EM express tool.
Larry Blankenship
-
I've just tried Daniel Bates' solution (.\database), and that seems to work just fine for me. I was upsizing from an Access 2000 formatted database using Access 2003.
As far as I have been able to tell, my results are fine.
Hope this helps Craig.
Paul Hansson
-
THANX A TON MR BATES
WORKS SUPER FINE 🙂
-
This thread is a life saver. trust me.
Million thanks to Bates. Cheers !!!!
-
Thanks everyone.
It was so long ago that i asked this question, I can't remember how I resolved it. But now I'm using mySQL instead and it's working a treat :Wow:
-
IF someone has difficulties using the Upsizeing Wizard of Access03 (or 07), look into the Sql Server Migration Assistant (SSMA) for Access. This does a much better job, and provides info on what is wrong when things fail.
SSMA is provided by the Sql Server Team and seems to have been inspired by issues with the UW in Access.
-
Dick Schroth (4/14/2006)
I'm thinking that the problem may involve finding the correct name for the SQL Server instance. It would make it easier to troubleshoot if you can install the SQL Server Enterprise Manager client, which would probably show you what the instance was named.
Dick
If I'm understanding your statement correctly, the following is correct:
SQL Server Enterprise = ability to create difference instances
SQL Server Standard = only 1 instance available (1 server)
:unsure:
-
Where does one find this tool? MSDN ? Microsoft site? Somewhere on the SQL Server install CD ?
Steve
(aka smunson)
:):):)
Scott MacCready (4/24/2008)
IF someone has difficulties using the Upsizeing Wizard of Access03 (or 07), look into the Sql Server Migration Assistant (SSMA) for Access. This does a much better job, and provides info on what is wrong when things fail.SSMA is provided by the Sql Server Team and seems to have been inspired by issues with the UW in Access.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
www[dot]microsoft[dot]com/sql/solutions/migration/access/default.mspx
-
Actually, Standard can support 16 instances on a single physical server. IIRC, Enterprise is 64 instances/physical server.
MS effectively defines a physical server as any number of CPUs sharing a single mainboard.
It is possible to limit the cycle and memory allocations to each instance.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply