Link to home
Start Free TrialLog in
Avatar of keith66miners
keith66minersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of John
John
Flag of Canada image

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.
Avatar of keith66miners

ASKER

Not at all helpful John - this is a serious enquiry - is there anyone willing to hep?
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.
ASKER CERTIFIED SOLUTION
Avatar of John Gates, CISSP, CDPSE
John Gates, CISSP, CDPSE
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of zalazar
zalazar

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 ?
If you can post a little more information we can help further.

-J-
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
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
Glad it is sorted!

-J-