How do i connect to a SQL server table using VB5? 1

[joebickley]

Hi
I have set up a front end for a SQL 200 server using access 97 and i now want to use VB5 to do the same thing. I need to connect to a SQL server table in code to do some data processing. In access i used the VBA code show below. But when using access i have the tables linked so i just need to refer to the table name. How do i connect to the table with out this. here is my MS access VBA code

Dim findcar As Recordset
Dim db As Database
Set db = DBEngine.Workspaces(0).Databases(0)
Set findcar = db.OpenRecordset("tablewhatever", , dbSeeChanges)
vinsent = findcar![6FigVIN]
buildpsent = findcar![ProdStage]
engsent = findcar![Engine]
drvsdsent = findcar![Steering]
findcar.Close

I guess it should not be a million miles away from this i think i need to set up a connection but i do not know how. Pleas help

Joe bickley Office automation specialist
Land Rover UK

 

[petermeachem]

Brace yourself, it's a good deal different. I am doing exactly the same thing right now, except going to VB6.

To open the connection:-
sConn = "DRIVER={SQL Server};server=" & sServer & ";uid=" & sUserID & ";pwd=" & sPassword & ";database=" & sDatabase

Set cnn= New ADODB.Connection
cnn.ConnectionTimeout = 15
cnn.ConnectionString = sConn
cnn.Open

Dim csql As String
Dim rs As New ADODB.Recordset
csql = "SELECT tDescription,sPriceFirst,sPriceSecond FROM Mailing_Styles WHERE nlMailingType = " & iMergeStyle
lblInfo(0) = "No Description"
lblInfo(10) = "No Price First"
lblInfo(11) = "No Price Second"
rs.Open csql, cnn, adOpenStatic, adLockOptimistic
If Not (rs.EOF And rs.BOF) Then
lblInfo(0) = rs!tDescription
lblInfo(10) = rs!sPriceFirst
lblInfo(11) = rs!sPriceSecond
End If
rs.Close

You don't need to open and close the connection all over the place, once is enough.

If I help you, can I have a Land-Rover please, an old one would do (older the better actually) ! Peter Meachem
peter@accuflight.com

 

[joebickley]

Thanks peter

it worked great. Will try and sort out free land rover for you but dont hold your breath you may go a little blue.

Thanks again

Joe Office automation specialist
Land Rover UK