Help with ADODB connection

[xuldinga]

So I have this checkbox on a form, when its checked it's supposed to add a record to a table. When it's unchecked it deletes the corresponding record. I am working with Access 2003 on a WinXP pro box. I can get the connection open, but it says it can't find the refrenced table. It's there, but I might have messed up the connection. This is what i have so far:

Private Sub chkVet_Click()
Dim strsql As String
Dim con As Connection
Dim ID As String
ID = (Me!EmployeeID)
Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
strsql = "SELECT * FROM Employee_Vet"
rs.CursorLocation = adUseClient
rs.Open strsql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If chkVet.Value = True Then
rs.AddNew "EmployeeID", ID
rs.Update
strsql = "UPDATE Employee_Vet SET Employee_Vet.Vet_Class = 'Veteran' WHERE Employee_Vet.EmployeeID = '" & ID & "'"
Else
strsql = "DELETE FROM Employee_Vet WHERE Employee_Vet.EmployeeID = '" & (Me!EmployeeID) & "' AND Employee_Vet.Vet_Class = 'Veteran'"
End If

con.Execute (strsql)

rs.Close
con.Close
End Sub

 

[JerryKlmns]

I see that EmployeeID is a text field in your table and not a numeric one(...) and I trust your connection is valid.

Private Sub chkVet_Click()
Dim con As ADODB.Connection

Set con = New ADODB.Connection
con.CursorLocation = adUseClient
con.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"

If chkVet.Value = True Then
con.Execute ("Insert Into Employee_Vet (EmployeeID, Vet_Class ) Select ('" & Me!EmployeeID & "', 'Veteran');"),,adcmdText+adExecuteNoRecords
Else
con.Execute ("Delete From Employee_Vet Where ((EmployeeID='" & Me!EmployeeID & "') And (Vet_Class ='Veteran'));"),,adcmdText+adExecuteNoRecords
End If
con.Close
Set con=Nothing

End Sub

 

[xuldinga]

Well I tried it and I got the following:

run-time error '-2147217887 (80040e21)':
The system cannot locate the object specified

and it stops at the line with the following:
con.Execute ("Insert Into Employee_Vet (EmployeeID, Vet_Class ) Select ('" & Me!EmployeeID & "', 'Veteran');"), , adCmdText + adExecuteNoRecords

I know the table is there, I have querys running off it elsewhere. However, I don't have any coded connections to it. If I open a record set to it and add a new record it works though. I have no idea what could be wrong.

 

[PHV]

And this ?
con.Execute ("INSERT INTO Employee_Vet (EmployeeID, Vet_Class) VALUES ('" & Me!EmployeeID & "', 'Veteran')")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886