vb6 on windows 10 interface with sql 2014

Experts
I have an old VB6 application that was last developed on Windows 2000 linking to SQL2005. I still have these boxes but also 2 new ones:-
 a windows 10 64 bit pc and a new server with sql 2014 on it.
My tables have moved to the new server (via backup on old, restore on new)
I have modified the vb6 code so as to allow selection of which server I want to run on.
I have ODBC connection established
This is perfectly OK for the windows 2000 - able to link to both old and new servers and execute the code - in particular:-

global rs_members as recordset
sqlq = "select * from mytable"
set rs_members = Dbgeneral.OpenRecordset(sqlq, dbOpenSnapshot)

This code fails with "Run-Time error 3146 ODBC call failed"when running on windows 10 vb6 (simply as Run with full compile)  when linking to new 2014 sql.

It works perfectly OK when running on windows 10 linked to old server.

So persumably there is something in sql2014 that is causing the problem.
Any ideas?
Thanks
Keith
keith66minersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

JohnBusiness Consultant (Owner)Commented:
So presumably there is something in sql2014 that is causing the problem  <-- Yes. VB6 is long out of support, and you say your application is even older. So you need to modernize to something newer.
keith66minersAuthor Commented:
Not at all helpful John - this is a serious enquiry - is there anyone willing to hep?
JohnBusiness Consultant (Owner)Commented:
Let's see what others say. I understand your enquiry was serious, but software does wear out, hence my reply. If anyone has a solution, I would certainly be interested in knowing it.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

John Gates, CISSPSecurity ProfessionalCommented:
On the 2005 SQL box are you running an extension that maybe needs to be on the SQL 2014 box to support the application?  I would also be curious to see the connection strings for the connection (not with the real passwords of course) maybe the connection string is not well formed for sql 2014.  That ODBC call error usually means the connection string or credentials are where the problem exists.  When you moved the database did you also move all the login credentials?

-J-

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
zalazarCommented:
Did you maybe already check if you are using the correct ODBC configuration.
With 64-bit Windows you have a 64-bit ODBC but also a 32-bit one.
Can you tell which one you are using ?

Are you using the default SQL Server ODBC driver ?
If so, did you already try to install the SQL Native Client and use this ODBC driver ?
John Gates, CISSPSecurity ProfessionalCommented:
If you can post a little more information we can help further.

-J-
keith66minersAuthor Commented:
Experts

The connection string is set as follows:-
strcn = "ODBC;FileDSN=SERVERConnection;UID=sa;passwd='fred';database=MYdatabase"
set myworkspace = DBEngine.CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
set Dbgeneral= myworkspace.OpendConnection(", dbDriverNoPrompt, False, strcn)

This code is effective for all 4 scenarios (old and new dev box to old and new server)

The next statements all work in all 4 scenarios
sqlq = "select @@servername"
set server_n = Dbgeneral.OpenRecordset(SQLQ, dbOpenSnapshot)
server_n.movefirst
server_name = server_n(0)

The next statements are the crash point
sqlq = "select * from TABLE where member_type = 'p'
set rs_table = Dbgeneral.OpenRecordset(SQLQ, dbOpenSnapshot) - it is here that the new dev box records run time error 3146 ODBC call failed

ODBC uses Window Authentication.
The W10 dev box has a path c:\Program files\common files\ODBC\Data Sources\ where the connection strings are held - it does NOT have a path c:\Program files(x86)\common files\ODBC.
I have both ODBC 64 bit (path %windir%\system32\odbcad32.exe)
             and ODBC 32 bit  (path %windir%\syswow64\odbcad32.exe)
I believe SystemDSN and UserDSN are not used in my Application so although they have been set do not feature.

With regard to the Database differences:
Old database is SQL2000(80)  new is SQL2008(100)
As mentioned - the process to create the database on the new server was tasks -> backup (full backup) ; move file to transportation area then;  Tasks -> restore from backup.
I therefore believe that all login credentials have been moved/converted(?) - any hints how to check?

I think that's enough from this worn out programmer (the software's fit enough just been overtaken by events)
Regards
Keith
keith66minersAuthor Commented:
Rechecking of the login credentials highlighted differences. When these were corrected, system ran OK - until the next wall!
The trouble is - you can NEVER see over the buggers 'til you've scaled them
Watch this space for further developments
Thanks
Keith
John Gates, CISSPSecurity ProfessionalCommented:
Glad it is sorted!

-J-
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
Microsoft SQL Server

From novice to tech pro — start learning today.