How to edit queries listed in Object Explorer?

Toad Data Point
1

Hi,

I connected Toad DP to an Access database in hopes of using a much more robust development IDE (than the one offered by Access). That database has a couple of tables and several queries that were already created in Access. My problem is that I cannot find a way to access the SQL code of those listed queries (those in Object Explorer / List of Queries) in Toad DP for me to edit and launch.

Can you please advise?

Thanks.

2

You can right click and generate sql for create script on an Access table but it does not let you do that on an Access query. One would expect it to work the same as generate create or alter script on a SQL Server view but apparently not. Sorry, I am no help, can only confirm what you are seeing.

3

Thanks for your reply, GregDavis11009. Just to clarify - I’m not trying to generate a query “on top” of another query’s output, but to open up the SQL view of the queries listed in the Object Explorer, so that I can modify and run them.

I only hope that Toad DP doesn’t consider queries in connected Access database as simply a read-only “data source”, as this would defeat the purpose of what I’m trying to achieve by replacing Access with Toad.

4

I think that is exactly what your problem is. An Access query is basically the same thing as a SQL Server view (sql code precompiled for you that you can treat as a table). With views you can tell Toad to generate the alter or create script that will show you the code behind the view and then you can modify it. If you are not allowed to see it (access rights) you get a messge stating why it can not show you the script. With the Access Query the right click does not even show any of the Generate SQL choices. So it looks like Toad is not programed to do this for Access or it is restricted like you said.

5

Access queries are view able in a separate tab when connected to access. Are you able to see the queries and use them? Unfortunately we do not offer way to edit or add Queries

6

Thanks for your replies GregDavis11009 and Debbie Peabody!

Not that I’m delighted to learn that I now have to go back to editing Access db in its ugly IDE environment. I was really hoping I could find a workaround and use Toad instead, but alas… this doesn’t seem to be the case.

I wish Toad dev’s could add this functionality: I know many people who’re like me disgusted by Access’s lack of proper development tools. Toad DP could easily gain that hefty niche if its developers decided to add such simple functionality and market it accordingly.

7

Is there a specific reason you use Access? Our Pro edition has it’s own local storage database and includes snapshots which is a combination of query and table. Also we have toad views which are queries saved in TDP. I have not had many requests to add more support for access.

8

Debbie, for once - Access is by far the most widespread desktop database application which is easily available in most companies as part of default MS Office environment. Hence a lot of smaller “hand-made” corporate databases run in Access whether we like it or not.

In my case I received the database and the queries setup in Access from someone else, so converting everything into Toad DP would take way too much time, esp. with each new version update in the source Access database.

Toad team should really consider adding such functionality: all the nuts and bolts are already there in Toad DP, it’s just making them available so that users can work with Access in a transparent fashion, so that Toad can replace Access altogether as a development / analysis / maintenance environment, while only using Access as a back-end data source.

9

I am with the biggest toadster on being able to edit an Access query in Toad just like you can a SQL Server view but Debbie has a good point. If you are trying to manage several Access databases why not start replacing them one by one into a data warehouse? You can go into Access, copy the sql for the query and paste it into Toad and then use that as an inline table (subquery). But if you going to do all that, I would get rid of the Access databases or at least use them very sparingly.

10

I entered QAT-6384. No promises on when it will be scheduled:)

11

Thanks, Debbie!

Greg, I fully agree with you, but the main thing about MS Access is its ubiquity. If it were up to me, I’d also opt for using more sophisticated tools or building a fully-fledged DWH, but, alas, that’s out of reach in most cases when users need quick and easy solution and don’t have the money, skills or infrastructure to build anything more complex than a quick Access database. Which of course doesn’t mean that DB developers and power users in such cases should be cornered and have to resort to using very limited, ugly and non friendly development environment in Access.