We help IT Professionals succeed at work.

VB6 and SQL2008 Connection Issues

UdiHrant123
UdiHrant123 used Ask the Experts™
on
I have a number of older Visual Basic 6 programs that I must retain while moving to SQL2008.  I used ODBC connections with the SQL Native Client and went to edit the DataEnvironment Command properties to reconnect to my sql tables.  When I do this I find that the drop down box under object name no longer lists the tables and views that are in the sql database.

I am going to attach a document that will display where I am getting stuck.  
Any help will be greatly appreciated.
sql2008-and-VB6.doc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I assume, you installed the SQL 2008 natvie client on the clients system, right?

Usually there is no problem accessing 2008 databases as long as you use ADO.
But also tried your option to use OLEDB over ODBC.
This works fine (including your box) on a XP machine

So I assume, that either your underlying connection is not established, correct or not accessable, or there is something wrong with your ODBC definition. As you have installed a new client, I would check / recreate the ODBC definition first (note, that under 64 Bit systems, tehre are two ODBC settings, one for 32 Bit and one for 64 Bit). If you can connect in general, everything should be o.k.

You may try, if you are able to connect in general via ODBC (ODBC Console). Also you ha´be a Test button in VB.

But in general, why do you use ODBC, you can use ADO from visual basic just by changing the provider setting in you connection properties?

Author

Commented:
Thanks for yuor answer.  It has generated the following questions and clarfications.  Your patience is appreciated...

1 - I did not install anything new on the client PCs but did find the SQL native client option when building the new odbc connections.
2 - I'm not sure where to choose ADO rather then ODBC.  Here is my current connection string:

Provider=MSDASQL.1;Password=########;Persist Security Info=True;User ID=##;Data Source=Jen2010 Prod;Initial Catalog=TmsEPrd

3 - The TEST button inside of VB does work ok now - the error I'm getting is 'invalid object name' when I try to connect with a created 'view'.

Thanks again.

Author

Commented:
Here is some further detail - when I go to run sql builder inside of VB6 I actually get a message stating that I am connected to a SQL server newer then SQL2000 which is true.  I don't suppose Microsoft provided some sort of patch to make VB6 fully functional with SQL2008?  Error documented in attachment.
sql2008-and-VB6.doc
The SQL native client usually installs newer dirvers to the OS. As SQL 2008 has new functions and also other functions are disabled or not supported anymore, you have to make sure, that your SQL ODBC driver fully supports SQL Server 2008.

Go to Administrative Tools and Open ODBC Administrator, under the tab "Driver" you will find the drivers installed on the machine.
My driver versions are

WIN 7: SQL Native 2005.90.4035.00 and SQL Server 6.01.7600.16385
works from VB for both, SQL native or SQL Server Driver.

Win XP: SQL Native: none and SQL Server 2000.85.1132
works from VB

Enclosed you find what VB says to the last one, but is the same on Win7 with the other drivers.
VB-Connection.JPG
OK, the second....

In the fist picture, you see OLEDB for ODBC. There is also a OLEDB for SQL Server setting you can use, which is ADO.
ADO is more flexible and the newer technology.
VB-Connection2.JPG