Getting the SQL connection in VBA

[roytap]

To All,

My company is currently exploring on the extendability of GP using VBA. But i have problem even connecting to the SQL database. I am wondering whether there will be anyone who can assist me on this matters.

The coding that i used are as follows:

Private Sub CustomerID_AfterLostFocus()
Dim tmpQuery As String
Dim tmpID As String

tmpID = CustomerID.Caption
tmpQuery = "SELECT * FROM RM00101 WHERE CUSTNMBR='" + tmpID + "'"

If chkPriceLevel(tmpQuery) Then
MsgBox "True"
Else
MsgBox "False"
End If
End Sub

Private Function chkPriceLevel(ByVal tmpSql As String) As Boolean

Dim cnTest As Connection
Dim rsTest As Recordset

' On Error GoTo Err_Handler
' Set cnTest = New Connection
' Set rsTest = New Recordset

With cnTest
.Provider = "SQLOLEDB"
.ConnectionString = "Password=password;User ID =sa;Initial Catalog=TRAIN;Data Source=TRAINING"
.Open
End With

rsTest.Open (tmpSql), cnTest

If rsTest.EOF = True Or rsTest.BOF = True Then
chkUser = False
Else
chkUser = True
End If
cnTest.Close

End Function ''

The problem lies in cnTest property which VBA does not recognise. I have tick the latest DAO under the Reference, but it still doesn't work.

Any help will be appreciated.

Regards,
Roy

 

[DougP]

Got this from the Dynamics VBA book

Dim cn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As New ADODB.Command

With cn
.ConnectionString = "Provider=MSDASQL;Data Source=Dymanics;User ID=sa;Password=;Initial Catalog=TWO"
.Open
End With

cmd.ActiveConnection = cn
cmd.CommandType = adCmdText

''''''''''
It uses ADO instead of DAO so you need to refer to the Microsoft ADO reference
DougP, MCP