Unable to execute stored query!

OzGrid / Et Al,

I think I'm just being dense here.

I have a table that:

Name: tbl_Coat_ResistCost
Field1: ResistCost ( Text, 50 )
Field2: CostPerLiter ( Number, Double, 2 )

I have an INSERT query of: ( it works when ran from Access )
INSERT INTO tbl_Coat_ResistCost ( CostPerLiter, ResistType )
VALUES ([Tgt ResistCost], [Tgt ResistID]);

Now in excel I've got this routine which wants to use the above query to add new values.

Sub UpdateResistCost(strResistID As String, strResistCost As String, Optional blnAddResist As Boolean)


'   Local Variables
    Dim adoRst          As ADODB.Recordset
    Dim adoCmd          As ADODB.Command
    Dim adoPrm          As ADODB.Parameter


'   Data on this sheet dependent on data queried from database ( create a readonly recordset attached to the database )
    Call OpenCloseDatabase(madoConnRW, "OpenRW")


'   Get all available "PRG ID's"  ( qry_CoatPrg_AllPrgIDs )
    Set adoCmd = New ADODB.Command
    With adoCmd
        .ActiveConnection = madoConnRW
        If blnAddResist Then .CommandText = "qry_CoatPrg_AddNewResist"
        If Not blnAddResist Then .CommandText = "qry_CoatPrg_UpdateResistCost"
        .CommandType = adCmdStoredProc
    End With
    Set adoPrm = adoCmd.CreateParameter("Tgt ResistID", adVarChar, adParamInput, 50)
    adoCmd.Parameters.Append adoPrm
    adoCmd.Parameters("Tgt ResistID") = Trim(strResistID)
    Set adoPrm = adoCmd.CreateParameter("Tgt ResistCost", adDouble, adParamInput, 2)
    adoCmd.Parameters.Append adoPrm
    adoCmd.Parameters("Tgt ResistCost") = CDbl(strResistCost)


'   Execute database request and return data
    Set adoRst = New ADODB.Recordset
    [U]Set adoRst = adoCmd.Execute   [SIZE="6"]'  (ERRORS HERE)[/SIZE][/U]
    adoRst.Close
    Set adoRst = Nothing


End Sub

The error that I get is in the attached file.
"Data Type Mismatch"

Now I've been trying to change the "adDouble" because I'm 99% certain it's not the "adVarChar". Yes, the calling routine verifyes the appropriate values of the first two incomming values.

The "blnAddResist" value is "TRUE" at run time.

The routine which creates the connection follows. I've highlighted the section that creates the connection object in this instance.

Sub OpenCloseDatabase(adoConn As Object, strConnect As String)


'   Local Variables
    Dim strDataBase             As String, strConnection            As String


'   Step 2 : Set connection variables for database object
    strDataBase = Worksheets("Application Data").Range("ad_Database_PhotoInfo").Offset(0, 1) & Worksheets("Application Data").Range("ad_Database_PhotoInfo").Offset(0, 2)
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strDataBase & ";"


'   Step 2 : Open link to database READONLY
    If strConnect = "Open" Then
        If Not adoConn Is Nothing Then Exit Sub
        Set adoConn = New ADODB.Connection
        With adoConn
            .Open (strConnection)
            .CursorLocation = adUseClient
        End With
    End If


[SIZE="4"][B]'   Step 2 : Open link to database READWRITE
    If strConnect = "OpenRW" Then
        If Not adoConn Is Nothing Then Exit Sub
        Set adoConn = New ADODB.Connection
        With adoConn
            .Open (strConnection)
        End With
    End If
[/B][/SIZE]
'   Step 3 : Close linke to database
    If strConnect = "Close" Then
        If adoConn Is Nothing Then Exit Sub
        With adoConn
            .Close
        End With
        Set adoConn = Nothing
    End If


End Sub

Re: Unable to execute stored query!

A,

Not sure how that would work. And now not being at work won't be able to try it. Though I've never had to do that before. It seem to me as if the I was indicating the wrong type of field value.

The "[]" in the queries indicate to Access that there is supposed to be a entered value. That way you're queries are dynamic and not static.

Will look into this next week at work.

Re: Unable to execute stored query!

Bump!

Anyone...Anyone

Re: Unable to execute stored query!

Filippo,

Thanks for the suggestion. I'm trying to keep all queries stored in the database. So that way if/when I port to a more robust database ( SQL most likely ) that I can use stored queries. So I want to create and keep all queries in it's respective database.

Though this will be my alternative if I'm not able to determine the cause of the error.

Re: Unable to execute stored query!

OzGrid / Et Al, ( Flippo / Will / Shark )

I usually have so much integration with most of my projects that tearing out the problem areas is usually not an option. But . . .

Attached find 2 .zip files. In one is a workbook which has a form ( there's no macro to show it so it needs to be ran from the IDE ). It also has a text file that is export of the table from the database ( had to take the table out to get the file small enough to attach )

The other is the database ( minus the table of data )

1. Extract all files
2. Import the .txt file into a table called
_ tbl_Coat_ResistCost
3. Change the folder references in the routine on the form to point to the database where you put it.
4. Show the form
5. Middle tab
6. Lower Left Corner ( trying to update the cost of a selected resist )

I'm also going to post a thread at UtterAccess as well and point back to this thread. God willing someone will slap the light into my head! :confused:

Oh, and this is important too.
I've monkeyed around with stuff so much now that the original error isn't comming up. Now when this runs ( when trying to update a cost ) it executes all the way through ( with no errors ) but it doesn't actually change the cost in the table. Sorry to change the game plan on ya'll.