Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Tabledef Connect String overwritten

Posted on 2007-12-03
3
Medium Priority
?
619 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 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

715 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