|
Well,this is english portal. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
hi,
i use VB.NET to develop an application which uses local MDB files as databases,
my question is:
is there anyway that i can indicate if a certain mdb file is already opened by another user ( even in access client ).
the goal is if for example a user open 2 instances of my application and connect with both instances to the same mdb file, i would like to display a warning messege on the 2nd connection that there is already 1 connection opened to this particular mdb file.
thanks in advance!
Net
|
|
|
|
|
What I have done in the past, under a similar Access MDB requirement, is try to open the database exclusively, while trapping for the error if another user already has it opened, and then display the warning message if the error occurs. If the 'already opened' error did not occur, I would simply close the connection and then re-open the MDB shared.
|
|
|
|
|
Well you can improvise. Every time you check if some text file exists in the same directory as your databse. If it does not exists you create it. If it does exists that means that someone already opened the connection and you can implement code that generates warning.
Just don't forget to delete this file when you close connection.
As for the access client, every time you open mdb file with access or any other database managing software (this includes your connection that you create from your application) another file will be created in the same folder as your database. It will have the same name but different extension (.ldb). So you can combine this feature also with the one i described to get more detailed informatiopn.
I will try to code one example here(let us say your database name is "base.mdb"):
connection.Open();
if(File.Exists("C:\\opened.txt") == true && File.Exists("base.ldb") == true)
MessageBox.Show("Connection is opened by your application");
if(File.Exists("base.ldb") == true)
MessageBox.Show("Connection is opened by Access client");
Else
File.CreateText("C:\\opened.txt")
Of course dont forget to delete the text file after closing connection:
connection.Close();
if(File.Exists("C:\\opened.txt") == true)
File.Delete("C:\\opened.txt")
Any way this is the general idea. In a text file you could write much additional information on who opened the databse, when, etc..
Cheers!
|
|
|
|
|
i need the messege to be displayed even if the file is already opened by MS ACCESS.
Net
|
|
|
|
|
First, this is a race condition waiting to happen.
Second, applications don't always end cleanly. Sooner or later something will go wrong and you will end up with the "opened.txt" file existing even though no one has the database open, or the .ldb file can hang around if Access does not close cleanly.
|
|
|
|
|
You don't need to create a text file. Access creates a .ldb file to hold locking information when the .mdb is opened. Just look for that. 
There are some circumstances when Access does not create the .ldb file e.g. the directory that the .mdb is in is set to read only. But then Access will only allow one user to open the database so the second attempt will generate an error or exception.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
hi,
Please help me..
i ve a table like this,
Sentby SentOn AcceptBy AcceptOn
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.753
32 2009-06-29 13:36:59.450 1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693 1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000 1 2009-06-16 19:40:36.000
and i need like this
Sentby SentOn
1 2009-06-15 19:40:36.000
32 2009-06-29 13:36:59.450
1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000
1 2009-06-16 19:40:36.000
here in 2nd & 6th row ,Sentby and Accept by Columns are different so i need that in next row.
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
Try this(Assuming that the original table name is tblSENTBYSENTON)
ALTER PROCEDURE dbo.GETRECORDS
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- VARIABLE DECLARATION
DECLARE @SENTBY INT
DECLARE @SENTON DATETIME
DECLARE @ACCEPTBY INT
DECLARE @ACCEPTON DATETIME
DECLARE @NEWTBLSENTBYSENTON TABLE
(
SENTBY INT,
SENTON DATETIME
)
-- STEP 1: DECLARE A CURSOR
DECLARE MYCURSOR CURSOR FOR
SELECT SENTBY,SENTON,ACCEPTBY,ACCEPTON
FROM TBLSENTBYSENTON
-- STEP 2: OPEN THE CURSOR
OPEN MYCURSOR
FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON
-- STEP 3: START THE LOGIC
WHILE @@Fetch_Status = 0
BEGIN
-- STEP 4: INSERT RECORDS INTO TABLE @NEWTBLSENTBYSENTON
-- BASED ON THE LOGIC PROVIDED
IF(@SENTBY = @ACCEPTBY)
BEGIN
INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
VALUES(@SENTBY,@SENTON)
END
ELSE
BEGIN
INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
VALUES(@SENTBY,@SENTON)
INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
VALUES(@ACCEPTBY,@ACCEPTON)
END
-- STEP 5: GET THE NEXT RECORD
FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON
END
--STEP 6: CLOSE THE CURSOR
CLOSE MYCURSOR
--STEP 7: DEALLOCATE THE CURSOR
DEALLOCATE MYCURSOR
SELECT * FROM @NEWTBLSENTBYSENTON
END
With the same input, I got the same output:
Sentby SentOn
1 2009-06-15 19:40:36.000
32 2009-06-29 13:36:59.450
1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000
1 2009-06-16 19:40:36.000
Hope this helps.
Niladri Biswas
modified on Wednesday, July 1, 2009 2:11 AM
|
|
|
|
|
Thanks for reply....
but i need a same result in select query without using Cursor,temptable,looping etc.,
because superior says it is a time consuming process.
Nothing is Impossible. Keep always Smiling... 
|
|
|
|
|
Try this
SELECT Sentby,SentOn
FROM tblSentBySentOn
UNION
SELECT AcceptBy,AcceptOn
FROM tblSentBySentOn
WHERE Sentby <> AcceptBy
For grouping the results use this
SELECT A.Sentby, A.SentOn
FROM(
SELECT Sentby,SentOn
FROM tblSentBySentOn
UNION
SELECT AcceptBy,AcceptOn
FROM tblSentBySentOn
WHERE Sentby <> AcceptBy
) A ORDER BY A.SentOn
Hope this helps
Niladri Biswas
modified on Wednesday, July 1, 2009 3:56 AM
|
|
|
|
|
Thank you very much....Thanks for your timely help...
Nothing is Impossible. Keep always Smiling...
modified on Wednesday, July 1, 2009 3:30 AM
|
|
|
|
|
• Atomic Studios just moved to a 15000 square foot facility with 3 times the shooting space of the former studio
• Atomic Studios never charges overtime
• The only studio that offers a 100% completion guarantee. If for what ever reason you do not finish your shoot or you forget a shot, you can come back and shoot for free
• Parking for up to 100 cars
• over 50 linear foot of shootable green screen
• over 80 linear foot of shootable White Cyc
• 48 linear foot of shootable black void
• sign up on our site and receive a complete filmmakers tool kit packed with all the releases forms and printable documents needed to make your next film or video.
• We now can provide over 20 ton of grip and lighting, steadicam, jibs, dollies.
www.atomicstudios.com
323-851-3825
|
|
|
|
|
If you wish to advertise on the site then go through the normal channels - don't spam the forums.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I heard that it doesn't take much in the way of brains to set yourself up as a filmmaker what with monkey point deals and all. Thanks for confirming that rumor with your post.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
I'm developing a quiz application and i want to access the questions randomly from my dadtabase. Is it possible? please help.
ML Lingwati
|
|
|
|
|
Create the primary key to your questions as an integer (ID), then have the random number generator select the corresponding ID.
|
|
|
|
|
Hi,
The random numbers can be generated in multiple ways.
But in all the cases the questions should have a unique QuestionId( a Primary Key)
Step 1:
Create a table (say tblQuestions) with QuestionId(int Primary Key), Questions(Varchar(50))
Step 2:
Insert some values:
QuestionId Questions
---------------------
1 Q1
2 Q2
3 Q3
4 Q4
5 Q5
6 Q6
7 Q7
8 Q8
9 Q9
10 Q10
11 Q11
12 Q12
13 Q20
14 Q25
15 Q30
Solution 1:
Suppose you have a fixed set of questions (say 1 to 30).
Fire this query
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = ROUND(((30 - 1) * RAND() + 1), 0)
where 30 is the upper limit and 1 is lower limit
Solution 2:
Suppose your question may grow to any length dynamically
Fire this query
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = CAST(RAND() * N AS INT)
Where N is any number.
Means if you have records(i.e. Questions) from 1 to 10, N = 10
if you have records(i.e. Questions) from 1 to 50, N = 50
if you have records(i.e. Questions) from 1 to 100, N = 100 etc.
Solution 3:
For the same above scenario, the following query can also be use
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = ABS(CAST(NEWID() AS binary(6)) %N) + 1
Where N is any number.
Solution 4:
Send the random numbers from your front end application to the backend either in INLINE SQL or in STORED PROC, and retieve the questions based on the QuestionId.
Hope this helps
Niladri Biswas
modified on Tuesday, June 30, 2009 11:26 AM
|
|
|
|
|
How i connect a database in the main server to a client machine i am using SQL 2003
|
|
|
|
|
jamith wrote: How i connect a database in the main server to a client machine i am using SQL 2003
You are? How interesting.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
You have that reversed, the client conection to the server - see connectionstrings.com
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear All,
I have established transactional with updatable subscription replication among two sql 2005 instance.
Server A as Publisher and Server B as subscriber, both have same db
the questions is how can i keep existing data into server b Database.
i have changed the status for "Action if name is in use to Keep existing object unchanged" in my publication, but still got no success?
your help is really appreciated
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Hi Experts
i Have Data of 2 millions of record i am using C#.NET
as a front end then suggest me which database is better SQL Server or MS Access .we have perform sorting and searching also on this database
.
Dinesh
|
|
|
|
|
I suggest you to chose SQL Server.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
MS Access can support even 2 million of records.
But considering other factors like
Feature SQL Server 2000 Access 2000
database size 1,048,516 TB 2 GB plus
linked tables size
objects in a database 2,147,483,647 32,768
user name length 128 20
password length 128 14
table name length 128 64
column name length 128 64
index name length 128 64
Number of concurrent users limited 255
columns per table 1024 255
table size limited 1 GB
number of indexes in a table 250 32
number of columns in an index 16 10
bytes per row 8060 2000
number of tables in a query 256 32
columns per SELECT statement 4096 255
nested subqueries 32 50
number of enforced relationships 253 32
(obtained from http://www.allinterview.com/showanswers/63402.html[^])
it is better to go with SQL SERVER.
However, cost is also a factor!
But if your requirement is not so high, you can go with SQL SERVER EXPRESS EDITION. which is free
But the license is limited.(hope 10 users)
Hope this answers your question.
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
