What is the best way to access SQL 2008 R2 from vb6 to a remote server defined in DSN

Hi!

I have a VB6 full application suite that we have migrated from dBaseIV to SQL 2008 R2.  Some modules have been re-written in VB2012 but many of the more complex ones are still in VB6.   In the VB6 apps, we are currently using MS ActiveX Data Objects 2.7 library and haven't experienced any KNOWN problems.

However, now I am trying to access a remote SQL Server.  No problem in VS2012.  I just changed the server settings and it works.  In VB6 it is not working, at least on my W7 dev machine.  

I have defined a DSN using the SQL Server Native Client 11 (also used 10) and it was successful.  However, trying to set one up using the SQL Server driver (SQLSRV32.DLL) does not work.

In VB6, when I try to connect to the database using the DSN, I get an error that the DSN contains an architecture mismatch between the driver and application.

I am sure I have something mismatched . . but I'm not sure what it is.  Any help would be sincerely appreciated!
lthamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
that error means that your vb6 app is 32 bit, and your DSN driver is 64bit driver.

to fix this, please try to open the 32bit version of the ODBC admin tool, and define the DSN there.
0
lthamesAuthor Commented:
I am running the 32 bit version of the admin tool (odbcad32.exe).  But could I be selecting a 64 bit driver by mistake?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no you cannot select a 64bit driver by mistake.

do you run the vb6 application in a compatibility mode?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lthamesAuthor Commented:
Should I be?  I am testing this in the vb6 environment.   But if I compile and run the executable (no compatibility mode) it gets the same error.

Running in the vb6 environment I'm not sure where the compatibility mode would be set.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you have been using the wrong odbc tool (wrong path).
see this support note from MS:
http://msdn.microsoft.com/en-us/library/ms712362%28v=vs.85%29.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lthamesAuthor Commented:
Thanks!  That did it.  When I opened this one on Friday it showed my DSN so I thought I had used the right one.   But when I removed and readded it this morning, it worked!
0
lthamesAuthor Commented:
Well, I thought that worked, but it really just got me past the first error.  

I wasn't sure if I should post a new question, but now this statement:
sqlConnection.open "cloud", "sa", "pass-xxxx"

gives me error [DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied.

The name of my DSN is 'cloud'.  I have triple checked that the user and password are the same I have entered into the DSN config, and when I test that it is successful.  

I am using Microsoft ActiveX Data Objects 2.7 . . is this correct?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sqlConnection.open "DSN=cloud", "sa", "pass-xxxx"
0
lthamesAuthor Commented:
That worked.  Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.