adOpenDynamic -- no value given ... requested param 2

[grndpa]

The message I get in the ACCESS 2003 sub within module1 is
"No value given for one or more requested parameters"

I am trying to map data from externally linked excel files (EmpComp and EmpPers) to a single ACCESS 2003 table (PS_PERSONAL_DATA).

the "rsIn.Open sqlIn, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly" works properly.

the "rsOut.Open sqlOut, CurrentProject.Connection, adOpenDynamic, adLockOptimistic" gives the err.description: "no value given ..." messages

One thought was that I was only allowed one ADOBD.RECORDSET at a time. So I tried using rsIn for both the SELECT and INSERT. That results in message "Operation not allowed when the object is open"

Any ideas? I'm stumped and my googling and reference books give no clue!

Here are what a believe to be the cogent parts of my code. (Ellipses, of course, indicate code I removed).

Regards,

Grandpa (grnadpa) Brian

Option Compare Database
Option Explicit

Sub BuildPSPersonal()

Dim rsIn As New ADODB.Recordset ' EmpComp, EmpPers spreadhseets
Dim rsOut As New ADODB.Recordset ' PS_PERSONAL_DATA database
Dim sqlIn As String
Dim sqlOut As String
...


On Error GoTo cmdPersonalError
sqlIn = "SELECT EmpPers.EepEEID, " & _
"... & _
"FROM EmpComp RIGHT JOIN EmpPers ON EmpComp.[EecEEID ] = EmpPers.EepEEID;"
rsIn.Open sqlIn, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
loopCount = 0
If Not rsIn.EOF Then
rsIn.MoveFirst
End If
Do While Not rsIn.EOF
aEMPLID = Trim(rsIn!EepEEID)
If Len(aEMPLID) > 11 Then
aEMPLID = Left(aEMPLID, 11)
End If
...
sqlOut = "INSERT INTO PS_PERSONAL_DATA " & _
"(EMPLID, NAME, FIRST_NAME, ...) " & _
"VALUES ("aEMPLID, ...)"
rsOut.Open sqlOut, CurrentProject.Connection, adOpenDynamic, adLockOptimistic '<-- ERROR OCCURS HERE
rsIn.MoveNext
Loop
rsIn.Close
Set rsIn = Nothing
rsOut.Close
Set rsOut = Nothing

Exit Sub
'
cmdPersonalError:
MsgBox "[Sub cmdPersonal_Click]: " & Err.Description

End Sub

 

[rac2]

Could you copy and paste the statement from your script into a code box here. Possibly there is an error in the VBScript that builds the SQL query, or possibly the query is not constructed correctly, that is wrong number of values for the columns listed.

sqlOut = "INSERT INTO PS_PERSONAL_DATA " & _
            "(EMPLID, NAME, FIRST_NAME, ...) " & _
        "VALUES ("aEMPLID, ...)"

See the guide to TGML which is linked in the Editing Tips message at the bottom of the Preview Post page to see how to create a code box.

 

[grndpa]

Here is a simpler one in its entirety where I get the same err.description.

The table name is "tryit"
The key column name is myID, text for length 11
The other column I named "somethingElse"is text, length 5 with default value "deflt"

Sub NowTryThis()
    Dim rsIn As New ADODB.Recordset     ' EmpComp, EmpPers spreadhseets
    Dim rsOut As New ADODB.Recordset    ' tryit database
    Dim sqlIn As String
    Dim sqlOut As String
    Dim aEMPLID As String
    Dim loopCount As Integer             'for debugging only
On Error GoTo NowTryThisError
    

    sqlIn = "SELECT EmpPers.EepEEID, " & _
            "EmpComp.[EecEmplStatus ] " & _
            "FROM EmpComp RIGHT JOIN EmpPers ON EmpComp.[EecEEID ] = EmpPers.EepEEID;"
    rsIn.Open sqlIn, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    loopCount = 0
    If Not rsIn.EOF Then
        rsIn.MoveFirst
    End If
    Do While Not rsIn.EOF
      aEMPLID = Trim(rsIn!EepEEID)
      If Len(aEMPLID) > 11 Then
        aEMPLID = Left(aEMPLID, 11)
      End If
      MsgBox "aEMPLID " & aEMPLID & " len " & Len(aEMPLID)
      
      sqlOut = "INSERT INTO tryit (myID) VALUES (aEMPLID)"
      rsOut.Open sqlOut, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

      loopCount = loopCount + 1
      If loopCount > 5 Then
        Exit Do
       End If
      rsIn.MoveNext
    Loop

Error occurs on this line

 rsOut.Open sqlOut, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

 

[rac2]

sqlOut = "INSERT INTO tryit (myID) VALUES ('" & aEMPLID & "')"

 

[PHV]

Replace this:
sqlOut = "INSERT INTO tryit (myID) VALUES (aEMPLID)"
With this:
sqlOut = "INSERT INTO tryit (myID) VALUES ('" & aEMPLID & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[grndpa]

So the only thing left is where to send the beverage of your choice <grin>.

You're a lef-saver. Thanks!