February 8, 2010 at 4:07 am
Hi
A real beginner question I suspect....
I have a longstanding Access 2003 database and want to upsize to a SQL database holding the data and where I can write SQL views to do the number crunching, but use Access for queries and reports.
I have experience of writing SQL queries on established (HR) databases at work, but have never created one from scratch.
I've installed SQL Server Express 2008 on my home PC (I am one of two windows users and have full admin rights) and used the Import Wizard to successfully create the tables in a new database, but cannot see them when trying to create a new ODBC data source to link to, from Access and get the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied
As an alternative I also tried the tutorial on here re upsizing an Access database, but get the same error message when trying to connect.
What "school-boy error" have I made?
Regards
HRGeek
February 8, 2010 at 7:53 am
Have you actually created a database? Just installing it isn't enough; you need to create/define/build a database once you've installed it.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 8, 2010 at 7:55 am
HRGeek (2/8/2010)
used the Import Wizard to successfully create the tables in a new database
Oops . . . that'll teach me to skim over a post! 🙂
What does your connection string look like?
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
February 8, 2010 at 8:08 am
I am not seeing that you have actually created a user account and a database. Installing SQL only installs SQL. You need to create a database, and then a user that will have access to that database. At that point you can being to create tables and load them with data.
I would not recommend using two separate databases. Create one, and use it. Trying to keep two SQL databases in sync is difficult enough, but trying to keep two different types of databases in sync would be a small nitemare.
SQL can do so much more than Access ever could. They are totally different, so don't expect to think that what works in Access will be the same in SQL Server.
The login that you have for Windows, does not matter in SQL Server. They are totally irrelevant. I would not recommend that you use Windows Auth unless you specifically are using Active Directory. Create a SQL user and log into SQL using that login.
Make sure that you create the sample databases, and for certain the SQL Books Online. Cannot do much without the BOL. The BOL are the best tool that you can have to learn how SQL operates.
Andrew SQLDBA
February 8, 2010 at 8:15 am
If you go into SQL Server 2008, can you see the tables? Can you write a query there and get the information you want? That'll verify that the import worked properly.
Have you created an ODBC connection on the machine?
If you go to Settings/Control Panel/Administrative Tools/Data Sources
you can create a connection to the SQL Server database that Access can then use.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 8, 2010 at 8:47 am
Hi Ray thanks for the response, yes the database auto-created with the data import and I can successfully write queries on it in SQL. There are currently 4 tables, but nothing else. See attached.
February 8, 2010 at 8:54 am
That is all the Access knows how to pump over to SQL. It does not know how to move the queries and created stored procedures. I would highly recommend going thru the database tables by hand and modifying things like some of the data types. SQL data types are much better than the ones that Access use. Also, you can create constraints between tables. you can then write the queries as stored procedures. You can also create a user login.
From there, you will much happier with the results, and the way that you can access the data in the database. When you now want to pump the data out, it is almost endless on the file formats, and how simple it will be to not have to use the old, slow ODBC connections.
Andrew SQLDBA
February 8, 2010 at 8:55 am
Hi
Yes all seems fine within SQL, the tables are there, I can write queries ok, it's setting up the OBDC links are you describe that throw the error I quoted.
This is going to be a steeper learning curve that I expected....
February 8, 2010 at 8:59 am
I've got loads of experience writing queries and views in SQL and am keen to build on this, using this exisiting database as the inital source of data.
I hope to be able to scrap most of the Access queries with newly programmed SQL stuff, merely using Access to "pretty up" to outputs........but am frustrated by not even being able to get out of the starting blocks at the moment.
February 8, 2010 at 9:13 am
You can now use SQL Reporting Service, and get rid of Access totally. You wan to see some wonderful slow downs, then keep using Access to access the data in SQL. That will be hugely slow.
SQL Server is much higher for permissions. First of all, you must set SQL to allow remote connections, by default it is turned off. Right click on the database box name, select "Properties", and then select "Remote Connections"
Create a SQL Login account on that database server, and a SQL User account on that database. Or give that login account permissions to access that database. I give my user account absolutely no permissions, except Execute to the stored procedures that I use. That is the most secure.
Then if you are still wanting to use access, you will need to create another, old slow, poor ODBC connection on that box, using the SQL Login account that you just created.
You can doo all of your reporting in .NET or SQL Reporting Service. There is so much more that you can do, and your users do not have to have Access installed or configured.
Andrew SQLDBA
February 8, 2010 at 10:14 am
Panic over guys - thank you all for your suggestions. The solution was that in SQL Server Configuration Manager / SQL Server Network Configuration / Protocols for SQLEXPRESS TCP/IP had been defaulted to Disabled in installation. Enabling has suddenly made everything work.
Andrew SQLDBA - thanks for the advice on SQL Reporting Services - I'll definitely give that a go once the queries are all set up - is this available within the FREE SQL EXPRESS setup?
February 8, 2010 at 10:58 am
I honestly am not sure. I have never used the Express flavor. I am thinking that it is in the latest version that you can download from MS.
Andrew SQLDBA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply