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

 

[RoryA]

Best make it a half of shandy for Jon, after last time...

 

[AlexanderBB]

Jon, that works perfectly too

Guess I misinterpreted those Miscrosoft instructions. You guys should be be writing them

One therefore concludes ADO vs DAO is a draw ?

Thanks again to you both,

Regards, ABB

PS Another question - can Excel get back from Access the query definition ? Then the modify routine
could just use Replace ??

 

[RoryA]

DAO is generally faster and easier so if you are using Access, I'd use DAO.

 

[Jon von der Heyden]

PS Another question - can Excel get back from Access the query definition ? Then the modify routine
could just use Replace ??

With the ADO one I have been on about, you would just look at the cmd.CommandText. Load it into a string variable if you wish, then do replacements, and then load it back into the CommandText.

One therefore concludes ADO vs DAO is a draw ?

What Rory said. Only because I lack the experience to know any different.

 

[RoryA]

similarly with DAO you can read the querydef's SQL property, run a replace on it and write it back.

 

[AlexanderBB]

Many thanks for last tips -which I'm yet to try as stuck on a really peculiar one.

When I first ran Jon's code using Views collection, it was fine. But the next and subsequent times I'm getting

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

on line

Set cmd = catDB.Views(strQryName).Command

This is the same error as the first version that referenced the Procedures collection. I've spent a hour or two trying to find what's changed or why it worked once and have come up blank.

Any thoughts, Jon ?

 

[Jon von der Heyden]

I've just created a mock-up and it works for me. I can run it several times and no problems...

Have you made any amendments (other than 'Views') to that sub?
What is going on between calls?

 

[AlexanderBB]

Jon,

It's similar dummy test I've set up here... nothings else is happening and I'm calling this from Excels immediate window.
In Excel -

Sub DoUC()
Dim tpath$, s$, n$
tpath = GetPath(ThisWorkbook.FullName) & "BackendII.mdb"
s = "SELECT tbl1978.Serial FROM tbl1978;"
n = "Query7"
Call jModifyQuery(tpath, n, s)
End Sub

Sub jModifyQuery(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 Views collection.
   Set cmd = catDB.Views(strQryName).Command
   
   ' Update the query's SQL statement.
   cmd.CommandText = strSQL
   
   'Save the updated query.
   Set catDB.Views(strQryName).Command = cmd
   
   Set catDB = Nothing
End Sub

Function GetPath(theName)
GetPath = Left(theName, InStrRev(theName, "\"))
End Function

and in Access Query7's SQL is

SELECT tbl1975.Serial FROM tbl1975;

Really puzzled why it worked once here (and works for you). But it is 2.30am here and may be clearer in the morning. Could resume tomorrow ?

Thanks, ABB

 

[Jon von der Heyden]

It's the same proc I'm using and I don't get an error. I'm also using mdb. Not sure to be honest.

Is the query still in view if you go directly to the DB?

 

[RoryA]

remember when I said "DAO is generally faster and easier so if you are using Access, I'd use DAO"...