Re: Database path from DSN (MSDE) (2nd. attempt)

In access, open the database exclusively. If the open exclusive fails you
know someone else is in still and they should log off.

In SQL Server, have a table that has a field that indicates all uses should
log off / no users can log on - noly the Admin can logon to do one thing in
this state -> daily rollover & reset the flag. Whenever a user logs on to
the application, if they are not logging on as Admin, it denies the logon by
checking this table. If a user is logged on, if the open a new firm to try
to start new work when the flag is set, they are told to finish their work
and log off. Perhaps use 2 fields in the table: LogoffRequired and
DailyRolloverInProgress.

Personally, I think you should design out the processes you have and the
need for the rollover concept. This kind of processing should have died out
by 1990. Having a DB Lock function also requires a DB unlock function for
admins in the application & this is prone to misuse.

I do appreciate that some systems do require the abillity to for example
edit data once entered to correct anomalies before it is posted. I have seen
banking systems that do this and nearly fell off my chair when it was
demonstrated as a good thing only recently. A better and different
philosophy is to post all transactions and require Reversals and Adjustment
postings so never - this leaves and audit trail in place and reducing the
chance for fraud. At the same time educating the people paying you can be
risky - if they are used to doing something that way and have done so for
years they often will not listen to the voice of sense and reason... unless
you talk about fraud.

What sort of path did you expect? The files are on the server so should be
relative to the server. In terms of access time, using a table in the
database (SQL Server) will be exceedingly faster and more secure.

The only conceivable time you might normally want to lock an Access DB is
when you take an automated backup - whatever you do, make sure you use the
Compact to backup file step first, *do not* copy the file then do a compact
and replace the original as the copy will often work if there is a user
still updating data in Access resulting in a backup file that is corrupt
(the mdb file is open for shared write, shared read). Doing the Compact
first requires exclusive access to the DB so guarantees the data has
integrity. You do not need to lock a SQL Server DB ever under normal
operation even for a backup operation - again, design out this requirement.

Post back if you need more input.

HTH
- Tim



"Peter Scholl" <delete.this.pscholl@xxxxxxxxxxx> wrote in message
news:vtkib19tmce9kj8l7dhe37hbvufgvjfdur@xxxxxxxxxx
> Tim,
> Unfortunately only the local path of the db on the server is returned
> (d:\databases\TestDB.mdf), I don't get multiple paths. However, if I
> put the name of the server obtained from the registry (or other APIs)
> in front, and remove the ":" I can construct the path. I'll experiment
> with this idea and am thankful for your reply.
>
>>Now comes the question: Why on earth would you want to know that in a
>>client
>>application?
> Well, maybe there's a better solution, but my application has to be
> *extremely* simple from the maintenance, installation and operation
> point of view. i.e. 100% maintenance free and I need to lock the DB -
> here's a rather lengthy explanation why.
> An "End-of-Day" is selected manually every day (very often by someone
> with absolutely no IT experience) and the DB must be locked for about
> 5-15 minutes for the automated daily postings and internal date change
> etc. Since there's no way to manually lock the DB (I use ODBC, no DAO)
> I use the old "lockfile" trick with a file located in the same path as
> the DB. This is opened in exclusive mode during the EOD and shared
> mode in all other cases. This is why I need the full path to the DB.
> If you know of a better solution that would work on Access97+ *and*
> MSDE I'm open to ideas.
> BTW, the application runs on Win98 upwards. It is *not* client/server
> but all workstations communicate real-time with each other using
> socket connections. Works on single pc, peer-to-peer or domain.
>
> Thanks again for your reply,
> Peter
>
> On Wed, 22 Jun 2005 20:01:35 +1200, "Tim" <Tim@NoSpam> wrote:
>
>>The path is in the master database... There can be multiple paths...
>>
>>try running a query as follows:
>>
>>select filename from master..sysdatabases where name = 'your db name'
>>
>>Now comes the question: Why on earth would you want to know that in a
>>client
>>application? This tends to indicate to me that you are possibly not using
>>the right tool for the right job. Nothing on the client side needs to know
>>this and should not know this for normal applications (security) ==> not a
>>normal application.
>>
>>- Tim
>>
>>
>>
>>"Peter Scholl" <delete.this.pscholl@xxxxxxxxxxx> wrote in message
>>news:9v1ib1dh7ogt3059pr9sn8nhh3j1v1k2mb@xxxxxxxxxx
>>> I'm trying to get the full path of my .mdf database from a DSN on a
>>> shared WinXP drive (running MSDE).
>>> I've tried the following: (using MFC6, SP6)
>>>
>>> SQLGetInfo(g_db.m_hdbc, SQL_DATABASE_NAME, &sMyDatabase, lSize,
>>> &bytesreturned);
>>> SQLGetConnectAttr(g_db.m_hdbc, SQL_CURRENT_QUALIFIER, &sMyDatabase,
>>> lSize, &lLengthReturned);
>>> SQLGetConnectAttr(g_db.m_hdbc, SQL_ATTR_CURRENT_CATALOG,
>>> &sMyDatabase, lSize, &lLengthReturned);
>>>
>>> All the functions return the name of the database but not the path.
>>> The path is not in the registry so I can't use those functions either.
>>> Any help would be very much appreciated
>>
>


.

Relevant Pages