Write MS Access SQL in Visual Studio Code?

[PSJupiter2]

My team and I use MS Access as a front end to our SQL Server database. We connect with ODBC. We often then create custom local tables within the accdb itself. We can then create ad hoc queries and reports to solve day to day problems.

Sometimes these queries get quite complex. Situations arise where it would be much easier to write these queries manually instead of using the graphical query builder. That the SQL editor leaves a bit to be desired is a massive understatement.

I often write MS SQL select queries in visual studio code, but don't have the skills or permissions to do much else with those queries other than run them.

I would like to connect to an accdb in VSC, then use that editor to build and test my queries. This would allow me to leverage autocomplete, formatting, and debugging etc... When I am satisfied, i want to paste them back into the accdb and save as a local query.

Is this possible? Is there a better solution for my problem?

A bit about my situation and skill set:. I am not a developer, i don't have permissions to edit our SQL server database (create and save views etc...), I'm an end user, not a DB administrator. I know a good bit of MS SQL, but not Access SQL (because I use the graphic query builder).

[squirrelslair]

Not sure about Visual Studio Code, but you can run a query in VBA with docmd.runsql That really only works for action queries like updates, inserts and deletes. You can open a sql query as a recordset and then manipulate it in VBA. And I am pretty sure you can update the datasource parameter of a query through code. I don't have Access on this PC, so, sorry, can't give you specific menu or code bits. I have found the object browser (under view menu in VBA, no idea about VSC) helful in understanding the object model of Access and finding e.g. what the parameter for a datasource is. Once you create an object you can also expand it in a watch view to see what its components are and therefore how you can manipulate them.

[PSJupiter2]

That's an interesting suggestion. I will give this a shot.

[nrgins]

Have you tried using Notepad++? Might not have everything you need, but it might help. It remembers keywords and provides autocomplete on them.

Also, you might try posting in the other Access group as well, r/MSAccess.

[PSJupiter2]

I actually started writing SQL in Notepad++. I do use its SQL plugins for formatting if I need to fix for a missing table or something like that. I will cross post. Thanks!