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/d...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)
"David Pierce" <(E-Mail Removed)> wrote in message
news:F3DCA171-6000-4905-9FD4-(E-Mail Removed)...
> 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
> --
> If your server / PC doesnt contain the word "Supermicro" then you have no
> idea what you are missing!
>
>
> "David Pierce" wrote:
>
>> 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!!!!!
>>
>> --
>> If your server / PC doesnt contain the word "Supermicro" then you have no
>> idea what you are missing!
Similar Threads
