Connect SQL Server 2000 with VB6

sanjna000 · #1 (**permalink**) May 2nd, 2006, 05:39 AM

Hi there,

I have a VB6 program connected to Access 97. I migrated access 97 into SQL server 2000 and i am now in the process of linking SQL server 2000 database with VB6. I used the connection string and it seems to be working.

My problem is VB6 code uses opentable method to open access 97 tables. This has been used in many times in the code and i need to know how i can use the same code to SQL server 2000.
For example, at present it uses

Set MyTable1 = MyDb.OpenTable("client") for Access 97. How can i do the same for SQL server 2000?

I am very new to SQL server and i really appriciate u r help!

Thank you very much...

manudutt · #2 (**permalink**) May 8th, 2006, 02:01 PM

you need a lot of changes .. if you want to open a table and then use it you can use (in ADODB connection) -

Code:

Dim rec_item As New adodb.Recordset
...
rec_item.open ("SELECT * FROM item_main "), _
 connect, adOpenDynamic, adLockOptimistic

here connect is ADOBB connection.

if you want to update , delete any thing from table you can directly use SQL queries for that ,like

Code:

db.Execute "UPDATE item_main SET item_quan = item_quan   _
+ " & added_item & " WHERE item_no = " & cur_ino

you can ,however, use -

Code:

recordsetname.Add
recordsetname.Update

if you are using recordset ..
(NOTE : _ (underscore) is used to change line in VB)

Need to download code?

Navigation