Relevant background-
I'm a noob working my brains out for over a year into trying to make a database in MS SQL Server 2008 Express with the end idea for the front end being Access. After tons of reading and slaving over my schemas and three major revisions I'm finally ready to connect it to Access and I'm just striking out all around. The Microsoft Access IN and OUT book says it has instructions for this but they're on the included cd in the bonus material which seems to be the only part of the cd that will not work. Everything I've found on the internet hasn't gotten me there. The best I think I've found was an answer on this site but even the list of things to do given as the answer have me hitting some walls that I just haven't the foggiest of how to get through.
I'm going to lay these out and mention what I have and haven't done with each.
Just for background I'm running Access 2007 on a Vista machine that I'm pretty sure is up to date on the service packs (I should have 7 in a few days, it's in the mail finally) and I'm running SQL Server 2008 Express with the management studio.
Here's the answer that I was referencing--
The answer was given by the user "Renaud Bompuis" at the following link
http://stackoverflow.com/questions/635203/connect-access-2007-to-sql-server-2008-database
There should be no issue with connecting Access 2007 to a SQL Server 2008 database. You need to make sure that: 1. Your SQL Server 2008 database is accessible, ie that it isn't locked down and that it is accessible to the machine(s) where you will have your Access 2007 application. A few things to check: * In SQL Server 2008, go to Properties > Connections > Check "Allow remote connections to this server".I checked and the check box is checked to allow remote connections. Since this is on the same machine I don't know if this is vital, but whether or not it is it's taken care of to the best of my understanding.
* Enable TCP/IP in the Configuration Manager.
didn't think this was necessary since it's on the same machine but I did it all the same.
* Make sure the firewall allows incoming connections on TCP port 1433.
This is one thing I didn't do since I really couldn't see how a firewall would get in the way if both instances (the SQL Server Express and Access 2007) are on the same machine under the same admin login. But if I'm wrong on this please tell me how to go about altering things.
* You can also start the SQL Server Browser Service so your SQL Server instance can be found.
Did this, even restarted the machine, still can't get Access, nor the ODBC, to pull up the SQL Server 2008 instance on the machine. Nothing.
2. You have created an ODBC DSN (a System DSN) using Windows ODBC administration tool. If you're running on a 64 bit system, make sure that you're using the 32 bit version of ODBC to create your DSN, otherwise it will never be visible to Access which is a 32 bits application.
Went in there to make the system DSN and when I choose the SQL Server Native Client 10 thing and go to hit the drop down menu to choose the data source it pauses and then nothing comes up, nothing to choose from at all.
3. Once you have created the ODBC link (and tested it works) on the machine where Access is installed, you can just link the tables: In Access 2007, in the External Data ribbon tab > import > More > ODBC Database. Then select the DSN you create for your SQL Server 2008 database and chose which tables you want to link.
So clearly this last part I can't even try since I can't even get an ODBC link.
I have a feeling, being a self taught noob and all, that I'm probably missing something obvious to a professional or seasoned amateur but regardless of what my problem is it's driving me nuts. Having a good portion of the last year of my life put into this I'd really like to be able to make progress finally on the front end so that I can finally get some utility out of all my effort beyond just writing queries in SSMS.
Thanks in advance for any and all help anyone can give.