Solved

Tabledef Connect String overwritten

Posted on 2007-12-03
3
600 Views
Last Modified: 2013-11-27
I have been using DSNless Tabledefs in my Access application for a long time now. It uses these to attach to our SQL server. Today I noticed that my .mdb file was larger than I expected (40M) and that repairing and compacting didn't shrink it much. So I imported all the objects into a new database. The size came down 24M. I was happy for about 5 minutes.

My users got errors, which turned out to be because the DSNless tabledefs were now attempting to use a Trusted Connection. My code which creates the connection uses an explicit username and password and sets Trusted_Connection=No; In my tabledefs in the new database, the username and password have been removed from the Connect String, and Trusted_Connection now equals YES.

I tried recreating the tabledefs, but each time my parameters are being overwritten. My code sets the tabledef attributes to dbAttachSavePWD, and even this is being overwritten with a value of  537001984

Can anyone suggest why this is happening? It all worked fine before.

Ian

0
Comment
Question by:ipendlebury
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 total points
ID: 20395475
I'm not quite sure why the TableDefs are giving you issues but there is something you might try with your original .mdb (the one that was bloated) to bring it back down to size.

Create a shortcut with a path name that looks something like this, (I've provided several examples as I'm not sure which version of Office you're running).

Access 97:
"c:\program files\Microsoft Office\Office\msaccess.exe" /Decompile

Access 2K:
"c:\program files\Microsoft Office 09\Office\MSACCESS.EXE" /Decompile

Acess 2002:
"c:\program files\Microsoft Office\Office10\MSACCESS.EXE" /Decompile

Access 2003:
"c:\program files\Microsoft Office 11\OFFICE11\MSACCESS.EXE" /Decompile

Then take your Access.mdb file and drag and drop it onto the shortcut.

Once it opens perform a compact and repair.  There's a good chance you'll find much of the bloat is gone.  Not as much as you would get by importing the .mdb into a blank .mdb but possibly enough to prove satisfactory.  What's nice about this approach is that it is very easy to do again latter when the bloat reappears and it won't change anything about how your tables are configured.

May not be an answer to your question but possibly a solution to the bloat that created the issue regarding the tables in the first place.
0
 

Author Comment

by:ipendlebury
ID: 20395682
Hi Rick Thanks for the reply.

I think I would have preferred to find the cause of my problem, but your solution got the original database down to 25M. So i'll live with that. Thanks for your help.

Ian
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20395749
You're most welcome.  Wish I knew the answer to the tabledef mystery but I'm glad the alternative was acceptable.  Like I said, the best part about it is that you can reuse it quickly and easily whenever you like.

Rick
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question