Solved

Tabledef Connect String overwritten

Posted on 2007-12-03
3
576 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now