Re: Connect to SQL Server

Thanks Brandon,
I've tried Access connecting to SQL Server and that works OK.

"Brendan Reynolds" wrote:


I've only tested this in Access/VBA, but I don't think there's anything in
it that is incompatible with VB6 ...

Public Sub TestSqlExpress()

Dim strConnect1 As String
Dim strConnect2 As String

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Debug.Print "Using OLEDB Provider for SQL Server"
strConnect1 = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect1
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

Debug.Print
Debug.Print "Using SQL Native Client"
strConnect2 = "Provider=SQLNCLI.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=pubs;" & _
"Data Source=DBWGQZ0J\SQLEXPRESS"
Set cnn = New ADODB.Connection
With cnn
.ConnectionString = strConnect2
.Open
End With
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = cnn
.Source = "SELECT * FROM Authors"
.Open
Do Until .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
cnn.Close

End Sub

--
Brendan Reynolds
Access MVP


"Gary Watson" <GaryWatson@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E1C4D36-26FD-4252-899B-CF72312DBF78@xxxxxxxxxxxxxxxx
More code details.

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
strConnection = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;Data Source=" & cboServers.Text & ";User
ID=" & txtUserid & _
";Initial Catalog=" & cboDatabases.Text & ";Data
Provider=SQLOLEDB.1;Password=" & txtPassword

cnn.Open strConnection ' test out login





.

Relevant Pages

  • Re: Catastrophic Failure
    ... be a bug in the SQL Server ODBC driver rather than anything in your code. ... Dim cnn As ADODB.Connection ... Dim prm As ADODB.Parameter ...
    (microsoft.public.access.forms)
  • VB IDE Collating Seq error
    ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ... Dim strSQL As String ...
    (microsoft.public.vb.database.ado)
  • Lots of Data :-(
    ... I wrote a simple Visual Basic 6 program that updates a SQL Server table ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ...
    (microsoft.public.data.ado)
  • Re: data is not getting populated
    ... > Dim conString As String ... > Dim con As ADODB.Connection ... > Dim rst As ADODB.Recordset ... It sounds as though you are trying to bind your form to this SQL Server ...
    (microsoft.public.access.modulesdaovba)
  • Re: how to Trim Trailing spaces on SQL table
    ... Dim rst As New ADODB.Recordset ... Dim cnn As New ADODB.Connection ... Dim strSql As String ...
    (microsoft.public.access.adp.sqlserver)