Database Bloat (1 Viewer)

[John Gall]

Hi,
I am new to this group. I have searched high and low for an answer to what I'm sure is a very simple question.
Why when I run the code below does my database grow from about 800Kb to 50Mb and will not shrink even with compact and repair. The moment I switch this function off and use the linked table manager, it does not grow.

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
'Dim tdf1 As DAO.TableDef
'Dim Newserver As String
Dim tdfproperties As String
'Dim Position As String

Set dbs = CurrentDb()
' Loop through TableDefs collection, only processing
' the table if it already has a Connection property.
' (all other tables are local ... not linked.)
' In other words this will only change linked tables.
For Each tdf In dbs.TableDefs

tdfproperties = tdf.Connect

If InStr(tdfproperties, "efacdb") > 0 Then
tdf.Connect = "ODBC;Driver=SQL SERVER;" & "server=svr-db;" & "Database=" & "efacdb;& " & "Trusted_Connection=no;" & "Uid=efacs;" & "pwd=Password1"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

Look forward to some guidance.

 

[theDBguy]

Hi. Welcome to AWF!

That code, by itself, doesn't seem like it should cause the file to bloat (at least, I don't expect it to). We may need more information to guess at what's really happening there.

 

[John Gall]

Thanks for an amazingly quick reply.
I feel the same. It runs in the Autoexec macro before opening the main form.
I have used this code in many applications and always get the same problem.
The linked tables are in SQL for our main ERP system. As I mentioned, with this code left in, the compact and repair does not reduce the size. The moment I disable this function, it will compact.
What Additional info can I share to help?
John

 

[CJ_London]

why are you needing to refresh the links? Doesn't look like you are changing anything?

And usual question - this code is in the front end (you mention local tables) - I presume each user has their own copy and it is not being shared?

 

[theDBguy]

As I mentioned, with this code left in, the compact and repair does not reduce the size. The moment I disable this function, it will compact.

This part makes sense to me, since you said the relink code is in the Autoexec macro, which runs immediately after the C&R is complete.

 

[John Gall]

I'm only refreshing them to automate the password and save every user having it.
Is there a better way?
Yes each user (only 2) will have a copy

 

[theDBguy]

I'm only refreshing them to automate the password and save every user having it.
Is there a better way?
Yes each user (only 2) will have a copy

Hmm, if I am a user of this db, and I have my own copy of the FE on my own machine, I shouldn't have to keep changing my own password, right?

 

[CJ_London]

Just don't see the need for it - once you have linked a table it will only 'disengage' if something changes on the server.

But even then, why restate the connection, you could just loop through and refresh?

Only other thought is to use currentdb rather than dbs, saves creating another object

edit: did find this link which describes much the same problem - solution was to use passthrough queries (which is the way I was taught many years ago!)

MS Access 2003 Refresh Linked ODBC Tables in VBA Causes Bloat

Using the following code, it is bloating the database size when refreshing the linked ODBC table connections. As such, the user will never be be able to finish the process completely without closin...

stackoverflow.com

 

[John Gall]

When I remove this code, every time you open the db it asks for the SQL password for each linked database. Only one in this instance.
I guess this is for security of SWL data
mill look at simply refreshing.

 

[John Gall]

Hmm, if I am a user of this db, and I have my own copy of the FE on my own machine, I shouldn't have to keep changing my own password, right?

SQL seems to insist that the password is entered each time.

 

[theDBguy]

SQL seems to insist that the password is entered each time.

Ah, I see. There are two options for that. The first is to "store" the uid and pwd with the connection string, or use a login form and create a cached connection, which should not require refreshing/relinking the tables (if memory serves).

 

[isladogs]

Just out of interest, are you using Windows or SQL Server authentication in SQL Server?

 

[John Gall]

Hi, Sorry I did not acknowledge your last message last night.
SQL Server authentication.
I have tried saving the uid and p/w with the connection string but it still requests it each time.
As I mentioned in the first place, the code works but the database immediately goes from 800kb to 48Mb. I cannot see any reason why.

 

[isladogs]

Strongly recommend changing to Windows authentication. It is a much more secure approach.
All your problems should be solved. Authorised users will NEVER need to enter a password

 

[John Gall]

Thanks, I'll give t a try. I've always used SQL Authentication as its the "devil I know"