Connect to SQL DB (Just UPsized)

[DOA]

I have just Upsized and now it has errors on all of my "Set rst" Can someone exsplain and help me fix this problem. It Looks like the querys work but not the connections to the database.

SAMPLE below: It just grabs the window user ID and then checks the user table. If the users is not there it add the user and then it is done.

Private Sub Command3_Click()
Dim rst As Object: Set rst = CurrentDb.OpenRecordset("User_Table&quot
Dim Varxx As Variant
Dim VarXXX As Variant
Dim VarX3 As Variant
Dim FindID As Variant
Dim OrderNumX As Variant

VarXXX = fOSUserName()
If IsNull(VarXXX) Then
MsgBox " You are not logged in correctly so please Reboot computer"
Else
Varxx = "'" & VarXXX & "'"
VarX3 = DLookup("[UserID]", "User_Table", "[UserID] =" & Varxx)
If IsNull(VarX3) Then
With rst
.AddNew
!UserID = VarXXX
.Update
End With
Set rst = Nothing
Else
End If
End If
Set ord = Nothing
Call UpdateUser

End Sub

 

[GHolden]

Try going here

http://www.able-consulting.com/ADO_Conn.htm

This page give an example of connection string for just about any data source.

 

[DOA]

I looked at the site but what do you think would best work for me. I am connecting to a SQL2000 machine (called SQL2000) from win2k machines. I am running split security because that was the only way to upsize using MS access 2000. Do I have to set the password in the VBA code. An example would be great to replace this.

Dim rst As Object: Set rst = CurrentDb.OpenRecordset("User_Table&quot

Also will the VBA commands work on SQL2K, example.

VarX3 = DLookup("[UserID]", "User_Table", "[UserID] =" & Varxx)
If IsNull(VarX3) Then
With rst
.AddNew
!UserID = VarXXX
.Update

Thanks Again

 

[GHolden]

we have set sql security Authentication to windows only and created users accounts in sql for each user, then set permissions on the sql side. This means sql does the work for you. Code below should help with the Database connection. We are using NT and vb6 I have checked the following works in Access 97 though.


'dim global var
Public gcnnDB As ADODB.Connection

'Sub to open connection
Public Sub mOpenDB
On Error GoTo ErrorHandler

Set gcnnDB = New ADODB.Connection

' do all processing on the server
gcnnDB.CursorLocation = adUseServer
' timeout value
gcnnDB.CommandTimeout = 30


gcnnDB.Open "Driver={SQL Server};" & _
"Server=PROD006;" & _
"Database=smart;" & _
"Uid=;" & _
"Pwd=;"


Dim rstContractor As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblContractor"

rstContractor.Open strSQL, gcnnDB, adOpenStatic, adLockOptimistic

Exit Sub

ErrorHandler:

MsgBox Err.Description, vbCritical + vbOKOnly, "Establishing connection to data server"


End Sub


'Using Connection

Private Sub mUseConnection
Dim rstContractor As New ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblContractor"

rstContractor.Open strSQL, gcnnDB, adOpenStatic, adLockOptimistic

'Do whatever with recordset

rstContractor.Close
set rstContractor = Nothing

end sub

Hope this helps in some way.