Prev: Calculating comboboxes
Next: How to find the name of the present subform
From: Stefan Hoffmann on 31 Mar 2010 11:48
hi Mat,

On 31.03.2010 17:01, mat wrote:
> What client tools are you referring to? The Access db has been repaired
> etc.
From the SQL Server CD/DVD-ROM...


mfG
--> stefan <--
From: Alex Dybenko on 1 Apr 2010 09:55
Hi,
Also try to use {sql server} instead of {SQL Native Client}

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"mat" <mat(a)notarealdotcom.adr> wrote in message
news:MPG.261ae24a77e1398e9897bd(a)msnews.microsoft.com...
> Using Access 2003 I've been able to use passthrough queries to sql
> server 2005 for a long time. In setting up a windows 7 64 bit
> workstation I'm having issues that I can't resolve so far.
>
> Normally a connections string like this would work:
>
> ODBC;Driver={SQL Native Client};Server=blink
> \sqlexpress;Database=mydb;UID=test_login;PWD=secret
>
> That connection uses sql auth. All I've changed is the server name (old
> instance and new instance both run against local copies of sql express).
>
> I suspect that it's the 64 bit vs 32 bit driver thing, which I don't
> really have a handle on. When I'm using a dsn I have to start the 32 bit
> version of the odbc connection manager to get dsns that Access 2003 can
> 'see' when linking. Is something like this in play here? I've searched
> but not found any solution for this yet.

From: mat on 4 Apr 2010 12:12
In article <eKo8d100KHA.4168(a)TK2MSFTNGP02.phx.gbl>,
sylvainlafontaine2009(a)yahoo.ca says...
>
> After having read this thread, it's not clear if your problem is related
> only to DSN-Less connection or if you cannot connect even when using a DSN
> created with the 32 bit version of the ODBC control panel.
>
> First, Access is a 32 bit application, so it can see only DSN that have been
> created the 32 bit version of the ODBC panel and it can use 32 bit drivers
> only (with or without a DSN). Normally, both the 32 bit and the 64 bit
> versions of the ODBC SQL drivers should have been installed, so you
> shouldn't have any problem connecting from Access. Also, when creating the
> DSN, the result should be successful if you test it.
>
> There are two versions of the ODBC Native Driver for SQL-Server; one for
> 2005 and one for 2008 and for the 2008 version, you must add the number 10
> after it: {SQL Server Native Client 10.0} instead of {SQL Server Native
> Client}; so maybe this is your problem. (See
> http://www.connectionstrings.com/ ).
>
> Try reinstalling both the 2005 and the 2008 Native Drivers:
>
> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
>
> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
>
> I don't remember if you need to install the 64 bit packages only or if you
> will have to install the 32 bit package, too. However, it should be easy to
> check using the 32 bit version of the ODBC panel after the installation of
> the 64 bit package.

There are a places where I did mention that dsn odbc is fine on the 64
bit box like "I don't have other odbc connectivity issues with this
workstation or server, using dsn, other than needing to use the 32 bit
odbc management tool." And someone else started focusing on sql native
client 10.0, and I told him that I'd explicitly said this was for a sql
2005 db. Anyways it's a long thread and not surprising if you missed
some of the back and forth.

What would be very intersting to hear, and which I've mentioned a couple
of times, is if any of you are able to use dsn-less connections with
regular connections strings on a 64 bit os without taking any extra
steps. I can only imagine that many have tried since 64 bit is not
exactly new. If anyone stated that they can use the exact same
connection string on 32 and 64 bit os to sql server 2005, it'd seem to
indicate the issue lies elsewhere; I already stated a couple times that
I'm not sure it's 64 bit that is the issue, only that it seemed to be a
reasonable point to explore. Reasonable because 64 bit os supports two
separate stacks of odbc drivers, and how can the simple connection
string I'm using inform the OS re which to use? Maybe because it's
called by a 32 bit process, I don't know, but you can see why I an
interested in this angle. Nothing else that I'm aware of is different on
this client; that connection string works fine on a lot of workstations,
to date all of which have been 32 bit.
From: Sylvain Lafontaine on 6 Apr 2010 15:07
"mat" <mat(a)notarealdotcom.adr> wrote in message
news:MPG.26225a38382ffeb89897c1(a)msnews.microsoft.com...
> In article <eKo8d100KHA.4168(a)TK2MSFTNGP02.phx.gbl>,
> sylvainlafontaine2009(a)yahoo.ca says...
>>
>> After having read this thread, it's not clear if your problem is related
>> only to DSN-Less connection or if you cannot connect even when using a
>> DSN
>> created with the 32 bit version of the ODBC control panel.
>>
>> First, Access is a 32 bit application, so it can see only DSN that have
>> been
>> created the 32 bit version of the ODBC panel and it can use 32 bit
>> drivers
>> only (with or without a DSN). Normally, both the 32 bit and the 64 bit
>> versions of the ODBC SQL drivers should have been installed, so you
>> shouldn't have any problem connecting from Access. Also, when creating
>> the
>> DSN, the result should be successful if you test it.
>>
>> There are two versions of the ODBC Native Driver for SQL-Server; one for
>> 2005 and one for 2008 and for the 2008 version, you must add the number
>> 10
>> after it: {SQL Server Native Client 10.0} instead of {SQL Server Native
>> Client}; so maybe this is your problem. (See
>> http://www.connectionstrings.com/ ).
>>
>> Try reinstalling both the 2005 and the 2008 Native Drivers:
>>
>> http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
>>
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
>>
>> I don't remember if you need to install the 64 bit packages only or if
>> you
>> will have to install the 32 bit package, too. However, it should be easy
>> to
>> check using the 32 bit version of the ODBC panel after the installation
>> of
>> the 64 bit package.
>
> There are a places where I did mention that dsn odbc is fine on the 64
> bit box like "I don't have other odbc connectivity issues with this
> workstation or server, using dsn, other than needing to use the 32 bit
> odbc management tool." And someone else started focusing on sql native
> client 10.0, and I told him that I'd explicitly said this was for a sql
> 2005 db. Anyways it's a long thread and not surprising if you missed
> some of the back and forth.
>
> What would be very intersting to hear, and which I've mentioned a couple
> of times, is if any of you are able to use dsn-less connections with
> regular connections strings on a 64 bit os without taking any extra
> steps. I can only imagine that many have tried since 64 bit is not
> exactly new. If anyone stated that they can use the exact same
> connection string on 32 and 64 bit os to sql server 2005, it'd seem to
> indicate the issue lies elsewhere; I already stated a couple times that
> I'm not sure it's 64 bit that is the issue, only that it seemed to be a
> reasonable point to explore. Reasonable because 64 bit os supports two
> separate stacks of odbc drivers, and how can the simple connection
> string I'm using inform the OS re which to use? Maybe because it's
> called by a 32 bit process, I don't know, but you can see why I an
> interested in this angle. Nothing else that I'm aware of is different on
> this client; that connection string works fine on a lot of workstations,
> to date all of which have been 32 bit.

The general impression from your first post was that you were unable to
connect from an Access installation running on a 64 bit OS. Now, it looks
that you are able to connect but that you question is about some "extra
step".

What's exactly this extra step that you are talking about and what are the
connection strings that you are actually using on the 32 bit OS and on the
64 bit OS?

--
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)


From: mat on 8 Apr 2010 12:30
In article <#Y8j$xb1KHA.2028(a)TK2MSFTNGP05.phx.gbl>,
sylvainlafontaine2009(a)yahoo.ca says...
> The general impression from your first post was that you were unable to
> connect from an Access installation running on a 64 bit OS. Now, it looks
> that you are able to connect but that you question is about some "extra
> step".
>
> What's exactly this extra step that you are talking about and what are the
> connection strings that you are actually using on the 32 bit OS and on the
> 64 bit OS?
>

It's not really a complicated question and I've outlined the issue many
times in this thread, including in my prev response to you. The actual
connection string I am not going to post on a public forum, complete
with password etc, but the original post contains a munged, completely
ordinary connections string that works on 32 bit. The 64 bit conn string
is the same; and fails. That's the question, why does it fail?
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Calculating comboboxes
Next: How to find the name of the present subform