ADO Connection question

[AlexanderBB]

Hi,

I have a connection between Excel and Access. From Excel, I query Access and use the Recordset method to put the data into a sheet.

Can I use this to somehow Update or Edit an actual query that's in Access ?

Please advise yes or no. I think its No !

Thanks, ABB

 

[AlexanderBB]

Thanks Jon, that was a useful link although it seems to suggest a Stored Query is the result - that is not visible in the Access User Interface.

As both as needed, I'll have to mule that one over carefully!

For fun I ran up Sub ModifyQuery as per the link just to see the result. I'm getting Compile error:User-defined type not defined on

Dim catDB As ADOX.Catalog

Is there a new reference I nede to add ? Don't see ADOX in anything in the list.

Thanks also rorya, that DAO example is beaut, but won't work from Excel.

 

[Jon von der Heyden]

I think it's Microsoft ADO Ext x.x for DLL and Security. I tried finding my project with the stored procedure to confirm, but I think it's on one of my external hard-drives.

 

[Jon von der Heyden]

You can also use late binding:

Set objADOX = CreateObject("ADOX.Catalog")

Although if it were me I would go with early. I'm quite certain now that I referred you to the correct reference in my prior post.

 

[RoryA]

Thanks also rorya, that DAO example is beaut, but won't work from Excel.

Yes it will.

 

[AlexanderBB]

Yes it will.

You know rorya, as I was typing that in I was thinking I bet someone will come up with a way.

But not with the ADO commands or methods I presume. So to apply it, have you a sample? My details -

'Path
tpath = GetPath(ThisWorkbook.FullName) & "BackendII.mdb"
'New SQL
s = "SELECT tbl1954.Serial FROM tbl1954;"
'Existing Query name
n = "Query7"
Rem Call ModifyQuery(tpath, n, s)

Jon, just about to add reference and try again

 

[RoryA]

I ran that from Excel as it is - just needs a reference to DAO set.

Adjusted for your example:

Function ModifyQuery(strDB As String, strQuery As String, strSQL As String)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Set db = DBEngine.OpenDatabase(strDB)
    Set qd = db.QueryDefs(strQuery)
    
    qd.Sql = strSQL
    db.Close
End Function

 

[AlexanderBB]

Jon,

Reference was fine but as expected -

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested
name or ordinal.

on line
Set cmd = catDB.Procedures(strQryName).Command

strQryName is correct and does exist, but is an Access-created query, not one stored in the Procedures collection.

 

[AlexanderBB]

Rory, I'm stunned. Staggered !! You're absolutely right..... works perfectly.

It's just what I was needing to do and was sure would be impossible.

Thanks a billion, also to Jon.

Beers all round

Cheers, ABB

 

[Jon von der Heyden]

Jon,

Reference was fine but as expected -

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested
name or ordinal.

on line
Set cmd = catDB.Procedures(strQryName).Command

strQryName is correct and does exist, but is an Access-created query, not one stored in the Procedures collection.

That's cos you have to use the Views collection.

Sub ModifyQuery(strDBPath As String, _
                strQryName As String, _
                strSQL As String)
   Dim catDB As ADOX.Catalog
   Dim cmd   As ADODB.Command
   
   Set catDB = New ADOX.Catalog
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath
   
   Set cmd = New ADODB.Command
   ' Get the query from the [B][COLOR="red"]Views[/COLOR][/B] collection.
   Set cmd = catDB.[B][COLOR="red"]Views[/COLOR][/B](strQryName).Command
   
   ' Update the query's SQL statement.
   cmd.CommandText = strSQL
   
   'Save the updated query.
   Set catDB.[B][COLOR="Red"]Views[/COLOR][/B](strQryName).Command = cmd
   
   Set catDB = Nothing
End Sub

 

[Jon von der Heyden]

Beers all round

Rory's is a guinness (a yard). Mine is a shandy. Thanks.