Access 2007 and connecting to SQL server 2008

  • Thread starter Thread starter David Pierce
  • Start date Start date
D

David Pierce

Hi all,
I am running Office 2007, Sql Server 2008 dev, and Visual Studio 2008 pro on
the same machine! Machine OS is windows Vista Ultimate x64. I cannot seam to
connect to my SQL server from access. Now, I have tried to Upsize with Access
(option 1 - Creat a new DB and this goes to select server, then name the DB,
and click OK, well, this then specifies a message that upsizing wizzard does
not work with version of SQL Server, contact MS for info and updates) Same
message with SQL 2005! (Option 2 - When I use existing DB, I have to
determine a file data source and DNS which I have not done as I dont know how
to do this).

I can however migrate any Access file to my SQL server 2008 by using the
import and export 32, that works just fine but I still cannot access my
database so that is mostly worthless.

I have also tried upsizing a new db and existing db and neither one work. I
have opend port 1433 and all tcp/ip connections have been enabeled. I have
read and read and read more stuff that does nothing to resolve this problem
than I can stand and still cannot connect by db to sql server.

Ok, so here we are and I need a solution, any solution that works at this
point is good for me. MS offers these products and recomends you buy them but
does not develope them to work togeither? Why on earth is that? In any event,
any help on the matter would be helpful. I am new to SQL server and have
order several books but I dcouldnt find any on how to connect access 2007 to
it so until someone writes that book its not available. Dont get me wrong, I
love MS and there products, I also like the sql server and designing tables
in that to work with my db, but I need to be able to connect to it or I just
spent 5 grand on nothing!

PLEASE HELP!!!!!
 
OK Gang,
Heres an update on SQL and Access

I have been involved in extremely painstaking task today and I have now got
Access 2007 to upsize to SQL Server Express and SQL Server 2005. I have come
ot the conclusion this is a SQL server 2008 problem with Access 2007. I am
hoping a update or hot fix will arrive shortly to allow me to use the
software I purchased and hope it works. Please let me know if you have any
other information or ideas as I would love ot use SQL 2008. Thank you all!
David Pierce
 
It was a painstaking task because you have made more whining in your various
posts than giving useful explanations about your situation. For example,
after reading your posts, I still didn't knew if the SQL-Server instance(s)
was/were located on the same or on a different machine and if you could
connect to them or not by using the SQL-Server Management Studio (SSMS)
tool.

This information is only a starting point because later, we must have to
deal with the choice of using between a windows credential or a SQL-Server
credential, the possibility of having either a default instance and/or one
or multiple named instances, knowing if the SQL-Server browsing service is
running or not and the possibility of having one or more firewall blocking
one or more required tcp-ip ports.

Upsizing and Connecting are two different problems. When your connection is
properly configured, any version of Access, including the very old Access 95
and 97, can connect to any version of SQL-Server, including the latest
SQL-Server 2008.

Upsizing is another thing because it's not only about connecting to
sql-server but also about having an automated tool that will recreate the
schema (design) of your tables on sql-server, copy over the data, restore
the relationships and change the Access database file so that the regular
tables are replaced with linked tables. This is a more complex task and
actually, the tool that came with Access 2007 cannot do this against
SQL-Server 2008.

However, this doesn't forbid you to use Access 2007 against SQL-Server 2008
but you'll have to do the upsizing process manually or copy the database
from SQL-2005 to 2008 or use another tool like the SQL Server Migration
Assistant 2008 for Access V4.0 that you'll find on
http://www.microsoft.com/downloads/...C2-C89C-4641-BEBB-6D04476EC1BA&displaylang=en

For the DSN, Access is a 32 bit application so it cannot access any DSN that
have been created in the 64bit registries. For this, you must use the 32
bit DSN administration tool that you will find on
windows\syswow64\odbccad32.exe. (Don't ask me why this repertory is called
syswow64 instead of syswow32.). Notice that you could also create and use
DSN-less ODBC connections in Access but I think that the automated tool can
only creates DSN-full ODBC connection. This is a limitation of the tool,
not of Access.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
If you had read the first line of my post instead of worrying about what I
was whining about, you would have read these are all on the same machine,
Thank you!

Also, the migration does work to bring tables to SQL Server just fine, but
if you cannot connect them in your access db, then what good is that I ask?
 
David Pierce said:
If you had read the first line of my post instead of worrying about what I
was whining about, you would have read these are all on the same machine,
Thank you!
Click to expand...

That's exactly my point: while reading many tens of posts every day, we
don't have the time to start diving into an ocean of words to find the
relevant information when it's buried under some useless rants.

Even when a post is well written, it's easy to miss some important
information.
Also, the migration does work to bring tables to SQL Server just fine, but
if you cannot connect them in your access db, then what good is that I
ask?
Click to expand...

Well, if you can solve your connection problem, this will become very good.

You have answered "Now that is help, thank you very much!" in another post;
does this mean that your connection problem is now solved?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Back
Top