Querydef connection string update (1 Viewer)

Hi everyone,

I'm working on an Access datbase with a Microsoft SQL Server back-end.

Unfortunately we have to use a service account to connect to the back-end, so to add a bit of security I'm trying to clear each query's connection string after every time it has been used (users will have a ACCDE version of the database so they can't access the VBA code), and at the closing of the main form I have the below code to reset all connection strings in the database:


A similar sub routine is used to set up all connection strings before populating temporary tables.

The problem, however, is that when I run the DoCmd.RunSQL lines immediately after setting up all the connection strings with the sub routine (I have also tried CurrentDb.Execute), it seems that the sub routine adding all the connection strings is not fully ready before the VBA goes on with running the code that updates the temporary tables (DoCmd.RunSQL), and as a result a window pops up with "Select data source" (because the connection strings are not yet in place).

I have been experimenting a bit with adding delays before the DoCmd.RunSQL lines, but it seems the VBA code continues to execute regardless of this.

Does anyone know of a way to circumvent this problem?

Thank you

Out of curiosity, what kind of delays are you using, DoEvents, DbEngine.Idle or something equivalent?

I have tried the methods here:

https://www.access-programmers.co.uk/forums/showthread.php?t=167470

Try refreshing the querydefs collection after making the change.

How can I do that? I don't see any Refresh method.

QueryDefs.Refresh Method (DAO)
Office 2013 and later Other Versions
Last modified: June 29, 2011

Applies to: Access 2013 | Office 2013

Not supported for this object.

Syntax
expression .Refresh

expression A variable that represents a QueryDefs object.

Remarks
You can't use the Refresh method with collections that aren't persistent, such as Connections, Databases, Recordsets, Workspaces, or the QueryDefs collection of a Connection object.

No but you can refresh the table defs, which is what I think Galaxiom meant to say

CurrentDb.TableDefs.Refresh

Minty said:

No but you can refresh the table defs, which is what I think Galaxiom meant to say

CurrentDb.TableDefs.Refresh

Click to expand...

Not what I had in mind. Was an off the cuff idea that was easier to try than research. I have shifted the connection of tables before but not a query. I recall that the refresh was important with the tables.

I also wonder about the whole idea of using it as a security strategy. If your data is that important it needs real security, not "security through obscurity".

With SQL Server you have the environment to host very good security. But instead of linked table the data transfer is acheived through executing commands on connections to retrieve recordsets that you set to the Recordset property of the form.

If you use SQL Security then encrypt the credentials in the VBA that creates the connection.

However the whole game becomes child's play if you are able to use domain authentication and permissions in SQL Server.

Hi,

In the end we managed to solve the problem with the updates. It turned out the sub that's adding all the connection strings didn't update all the queries plus some other minor issues, so it turned out it was not the kind of error I thought. Thanks though.

Galaxiom said:

Not what I had in mind. Was an off the cuff idea that was easier to try than research. I have shifted the connection of tables before but not a query. I recall that the refresh was important with the tables.

I also wonder about the whole idea of using it as a security strategy. If your data is that important it needs real security, not "security through obscurity".

With SQL Server you have the environment to host very good security. But instead of linked table the data transfer is acheived through executing commands on connections to retrieve recordsets that you set to the Recordset property of the form.

If you use SQL Security then encrypt the credentials in the VBA that creates the connection.

However the whole game becomes child's play if you are able to use domain authentication and permissions in SQL Server.

Click to expand...

The data is not really sensitive or anything, but still we wanted to implement some degree of security in order to prevent people from being able to mess around with the data. Anyway, this approach we tried was probably pointless, because by opening the accde file in notepad you can find the connection string with space between the letters, even though all pass through query connections strings were cleared.

This is the first project I'm involved in that uses an SQL Server back-end, so I don't know much about it, but we will see if we can look into a better solution. Thanks a lot for your input.