a query that works under MS Access but doesn't in VBA MS excel

[pawelch]

Hi everyone,

I have got the following query:

  sSql = "SELECT  MSysObjects.ForeignName, MSysObjects.Name" & _
        "FROM MSysObjects" & _
        "WHERE (((MSysObjects.Name) Not Like ""*_ChangeLog*"") AND ((MSysObjects.Type)=6))"

This code give mi names of tables stored in a database.

It works when I run it under MSAccess 2010, but it does not in MS Excel while the code connects itself to the database.
There is seamless operation between Excel files and Ms Access as the rest of the queries work fine.

When I perform a connection to the database on the line :

....
  Set dbrs = New ADODB.Recordset
  dbrs.Open Source:= sSql, ActiveConnection:=dbcon
....

I get an error:

Run-time error '-blabla' 
Automation error

Thank you in advance for any suggestions you come up with.

Cheers!

 

[boblarson]

Re: a querry that works under MS Access but doesn't in VBA MS excel

Well, the first thing that jumps out at me is the use of sSQL in the assignment and then sqlstr in the other code.

 

[pawelch]

Re: a querry that works under MS Access but doesn't in VBA MS excel

Hi Boblarson,

you are absolutely right - that might cause confusion. It is because I copied and pasted it from two different functions that use two different variable sets - but I edited it already and it should not be disturbing.

Thank you for your hint.

Also, thank you in advance for any help further

Cheers!

 

[boblarson]

I would use DAO for this since it is a system table you want to access.

What is the query for? Is it a select query or an action query and what is the desired result of running it?

 

[pawelch]

Hi Bob Larson,

In general, it is a select query - I want to build a general-purpose export-data file that exports data from excel to access. Its purpose is to to ask where to upload data stored in an excel file. So, IMHO, the best bet would be to create a list of tables and then with a small description of these tables - specify with a combo box or something, where data should be uploaded to.

Thank you everyone for your help and time.
Cheers.

 

[boblarson]

If it is a select query you don't need to run it to export it. It runs when exported.

 

[pawelch]

I will look into it. I think you mean that I need to do something like "do.cmd run query" hopefully it works.

Thank you for your help.

 

[boblarson]

I will look into it. I think you mean that I need to do something like "do.cmd run query" hopefully it works.

Thank you for your help.

No, you just export it. You use code to export it. you do NOT need to run the query, you do not need to run the query, you do not need to run the query. Did I mention you do not need to run the query? I emphasize that because a lot of people, and it would appear you are one, believe you have to run a query first before exporting the data. That is totally incorrect. All you do is run code to export the query (like DoCmd.TransferSpreadsheet or even using my code here. It doesn't need to open the query separately because when you run the code it does the running of the query for you behind the scenes as it exports.

 

[pawelch]

Hi Boblarson,

Come on, what I typed in is exactly what you meant. But presumably I did not use the correct code.

I meant that I understood what you were saying but used a wrong code expression. Anyhow, thanks for letting me know there are other coding solutions

I'll try to take advantage of your coding solutions

Cheers and take it more easy please