ADP Conversion Question

[Paul H]

Well, I've gone through a rather laborious effort of importing objects and recreating those I that wouldn't import. I'm not done by any means, but I have come across a stumbling block I hope you can shed some light on. When clicking on a command button on the main form I received this error.




Here's the full code. I didn't write any of this so I can't shed much light on it. There are other functions that contain the same syntax. Is there a library reference I need to select?

Public Function DeleteDuplicateSigEvent()
Dim cmd1 As ADODB.Command
Dim cmd2 As ADODB.Command
Dim cmd3 As ADODB.Command
Dim sSQL As String
Dim sSQL1 As String
Dim sSQL2 As String
Dim sMsg As String
Dim dtRD As Date
Dim dtSigDt As Date
Dim dtSigTm As Date
Dim sShift As String
Dim sTySig As String
Dim sPatLN As String
Dim sPatFN As String
Dim sWd As String
Dim sZeroShift As String
Dim sOneShift As String
Dim sTwoShift As String
Dim sThreeShift As String
Dim iCnt As Integer
Dim iCnt1 As Integer
Dim iCnt2 As Integer
Dim bDupSig As Boolean
Dim rsN As ADODB.Recordset
Dim rsEmp As ADODB.Recordset


' DoCmd.Hourglass True
sZeroShift = "0"
sOneShift = "1"
sTwoShift = "2"
sThreeShift = "3"
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandType = adCmdText
sSQL = "SELECT * FROM NewSigEvent WHERE Shift ='" & sZeroShift & "';"
cmd1.CommandText = sSQL
cmd1.Execute
Set rsN = New ADODB.Recordset
rsN.CursorType = adOpenDynamic
rsN.LockType = adLockOptimistic
rsN.Open cmd1
iCnt = rsN.RecordCount
If iCnt > 0 Then
   rsN.MoveFirst
   Do While Not rsN.EOF
      bDupSig = False
      dtSigDt = rsN.Fields("SigDate").Value
      dtSigTm = rsN.Fields("SigTime").Value
      sTySig = rsN.Fields("TypeSigEvent").Value
      sPatLN = rsN.Fields("PatLName").Value
      sPatFN = rsN.Fields("PatFName").Value
      Set cmd2 = New ADODB.Command
      cmd2.ActiveConnection = CurrentProject.Connection
      cmd2.CommandType = adCmdText
      sSQL = "SELECT * FROM NewSigEvent WHERE SigDate ='" & dtSigDt & "'" & "AND SigTime ='" & dtSigTm & "'" & "AND PatLName ='" & sPatLN & "'" & "AND PatFName ='" & sPatFN & "'" & "AND TypeSigEvent ='" & sTySig & "'" & "AND Shift ='" & sOneShift & "';"
      cmd2.CommandText = sSQL
      cmd2.Execute
      Set rsEmp = New ADODB.Recordset
      rsEmp.CursorType = adOpenDynamic
      rsEmp.LockType = adLockOptimistic
      rsEmp.Open cmd2
      iCnt = rsEmp.RecordCount
      If iCnt > 0 Then
         bDupSig = True
      End If
      rsEmp.Close
      Set rsEmp = Nothing
      Set cmd2 = Nothing
      Set cmd2 = New ADODB.Command
      cmd2.ActiveConnection = CurrentProject.Connection
      cmd2.CommandType = adCmdText
      sSQL = "SELECT * FROM NewSigEvent WHERE SigDate ='" & dtSigDt & "'" & "AND SigTime ='" & dtSigTm & "'" & "AND PatLName ='" & sPatLN & "'" & "AND PatFName ='" & sPatFN & "'" & "AND TypeSigEvent ='" & sTySig & "'" & "AND Shift ='" & sTwoShift & "';"
      cmd2.CommandText = sSQL
      cmd2.Execute
      Set rsEmp = New ADODB.Recordset
      rsEmp.CursorType = adOpenDynamic
      rsEmp.LockType = adLockOptimistic
      rsEmp.Open cmd2
      iCnt = rsEmp.RecordCount
      If iCnt > 0 Then
         bDupSig = True
      End If
      rsEmp.Close
      Set rsEmp = Nothing
      Set cmd2 = Nothing
      Set cmd2 = New ADODB.Command
      cmd2.ActiveConnection = CurrentProject.Connection
      cmd2.CommandType = adCmdText
      sSQL = "SELECT * FROM NewSigEvent WHERE SigDate ='" & dtSigDt & "'" & "AND SigTime ='" & dtSigTm & "'" & "AND PatLName ='" & sPatLN & "'" & "AND PatFName ='" & sPatFN & "'" & "AND TypeSigEvent ='" & sTySig & "'" & "AND Shift ='" & sThreeShift & "';"
      cmd2.CommandText = sSQL
      cmd2.Execute
      Set rsEmp = New ADODB.Recordset
      rsEmp.CursorType = adOpenDynamic
      rsEmp.LockType = adLockOptimistic
      rsEmp.Open cmd2
      iCnt = rsEmp.RecordCount
      If iCnt > 0 Then
         bDupSig = True
      End If
      rsEmp.Close
      Set rsEmp = Nothing
      Set cmd2 = Nothing
      If bDupSig = True Then
         rsN.Delete
      End If
      rsN.MoveNext
   Loop
End If
DoCmd.Hourglass False
rsN.Close
Set rsN = Nothing
Set cmd1 = Nothing
End Function

As usual, Thanks in advance.

Paul

[CJ_London]

you need to add the ADO library to your references

Microsoft ADO Ext6

[Paul H]

you need to add the ADO library to your references

Microsoft ADO Ext6

I added this on and still received the same error. Was this the right one?

[CJ_London]

should be, you should get intellisense one you have typed ADODB

[Micron]

Fairly certain it is Microsoft ActiveX Data Objects #.# library
My latest version is 6.1. I rarely have a need to use ADO. Regardless, if I add a library and I'm fairly certain that is the issue but it still won't compile, easy enough to remove the one that didn't help and try something else. Or I ask my friend Google...
https://docs.microsoft.com/en-us/sql...l-server-ver16

[CJ_London]

Fairly certain it is Microsoft ActiveX Data Objects #.# library

oops, think you are right - I always late bind so don't add references

[Paul H]

should be, you should get intellisense one you have typed ADODB

Fairly certain it is Microsoft ActiveX Data Objects #.# library
My latest version is 6.1. I rarely have a need to use ADO. Regardless, if I add a library and I'm fairly certain that is the issue but it still won't compile, easy enough to remove the one that didn't help and try something else. Or I ask my friend Google...
https://docs.microsoft.com/en-us/sql...l-server-ver16

Neither one of these references worked and no intellisense either.

[Micron]

Sub testReference()
Dim cmd1 As ADODB.Command
End Sub

Compiles for me - and I get Intellisense. Make sure you didn't do what I often do - select the reference from the list and forget to check the box for it.

[Paul H]

Sub testReference()
Dim cmd1 As ADODB.Command
End Sub

Compiles for me - and I get Intellisense. Make sure you didn't do what I often do - select the reference from the list and forget to check the box for it.

Doesn't work here. Not sure why. I've tried checking each one individually and both of them, but it does not compile on my end.

Attachment 48240

[Micron]

I get 'invalid attachment specified'.

[Paul H]

I get 'invalid attachment specified'.

Something went wrong when I posted my last comment.

[CJ_London]

maybe you have some corruption - create a new db and try a simple test there

[Welshgasman]

Perhaps compare references with the DB you imported from?

[Paul H]

Perhaps compare references with the DB you imported from?

Winner, Winner, Chicken Dinner!

It needed ActiveX Data Objects 2.1 Library

Now I have intellisense, etc.

This takes me a little further down the road. Now I have to rename all my tables. Fortunately I have a tool to do that.

Thanks again,

Paul

[Paul H]

I thought I was in the clear, but something new popped up. Every event on my main form now returns the following error: Expected Identifier. Even the On Load Event.
This was not happening on Friday, but I'm not aware of changing anything since then. In addition, I am unable to break the code to see what is causing it. This is all I get.



I sort of feel like I am back to the drawing board. Here's the code for the On Load event in case that helps.

Private Sub Form_Load()

bModSigEv = False
bNSE = False
Me.cmdSubmit.Visible = False
Me.cmdSubmit1.Visible = False
Me.cmdSubmit.Enabled = False
Me.cmdSubmit1.Enabled = False

End Sub

Thanks in advance

Paul H