Solved

Tabledef Connect String overwritten

Posted on 2007-12-03
3
605 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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