MS Access SQL Assistance

Hello.

I'm having challenges getting SQL Assistant working with MS Access. I had it working fine; however, I decided to try it out in NotePad++. Once I did that, I couldn't get the assistant to work in MS Access any more. I uninstalled SQL Assistant serveral times (including the User/AppData/Roaming/SQL Assistant directory); however, I can't get it to work. SQL Assistant used to work without any additional connection setttings (no connection popup); however, now the connection popup appears. I enter the connection string or the DSN and I receive an exclusive lock error (The database has been place in a state by a unknown user that prevents it from being opened or locked).

Any help?

Thanks,
Dan

Is the add-on for MS Access registered? If not, start SQL Assistant in Run as Administrator mode, open Options, select MS Access on the Targets tab, expand Advanced section on the right, turn off "Register SQL Assistant Add-on" option, click Apply, then turn it on again, and click Apply. Restart MS Access in run as Administrator mode and check f it works again.

PS. Please verify that Notepad++ still works as a target. Note that with MS Access query editor SQL Assistant uses direct MDB file connection, with Notepad++ it uses a regular database connection, in other words to connect Notepad++ to an Access database you would need to define an ODBC profile first and choose that ODBC connection with "MS Access" as the connection database type.

Thanks for the response. Yes, the add-in is registered; however, I re-registered in as described. Unforturnately, that didn't fix it. I receive a connection error:

CatalogQuery: ODBC Error (0): SQLAllocHandle
Client:
Server:
[Microsoft][ODBC Driver Manager] Connection not open

SQL Assistant still prompts for connection information. I believe the issue that I'm seeing is how to config SQL Assistant to use the direct MDB/ACCDB file connection.[/img]

Is that what you get in MS Access editor or in Notepad++

MS Access

Please verify you are not opening your Access database file in exclusive mode so that SQL Assistant and other programs can connect to it too.
Also please check the file has normal name and path, using only standard characters are used in the name.

The .accdb is not in exclusive mode and the path to the file contains neither special characters not whitespace. SQL Assistant was working fine with Access until I starting using NotePad as the editor. What should my connection related setting be set to? Current -
Registered Target: MS Access - Advanced:
- Auto Connection: None
- Default Connection Type: ODBC
- Default Command Timeout (sec): 60
- Show Coneection Context (sec): 5
- Register SQL Assistant Add-on: Yes

I'm certain the fact you use Notepad++ by itself is not a problem. There is some issue with the connection and it should be unrelated.

This value should be blank, not None. please highlight it in the settings and delete using the Del key.

	Quote:
	Auto Connection:

Does this help?

Unfortunately, no. That field does not allow an empty value. Highlighting and selecting the delete key changes the value to "Num Del." Once I select the Apply key, the value return to "None." Returning to Access and opening a query window and typing "SELECT " opens the connection dialog.

I tried another Access database and SQL Assistant is working as expected. Is there a hidden file some where that is retaining old settings per database - keeping the NotePad++ settings for all access to the specific database? It would have to be some file that doesn't get wiped when uninstalling SQL Assistant and does get overwritten on re-install. Thanks...

Please take a look at the DB Connections settings on the DB Options tab. If you see some bad connections there, try deleting them.