|
Working with Access 2000 Database and ADO(Visual C++)
My Database tables and their relationships resemble a Tree or a Family tree. (ie a lot of one-to-many relations between father/child tables)
The task is to keep the size of the database within certain limits. As the database grows beyond certain limit, i would like to keep the latest N records and delete the old stuff. Unfortunately, this doesnt seem to reduce the size of the database (I don;t know why) may be because I am using "Autonumber" fields.
The second step would be to reindex my autonumber fields so that I don't run over the limit of long integer datatype.
Please help with some tips on how to approach.
Thanks
|
|
|
|
|
You have to use "Compact and Repair Database" from the Tools->Database Utilities menu to shrink a database after records are deleted. This can also be done programmatically.
|
|
|
|
|
How to do it programatically ?
Any solution to "Autonumber" field running out of limits ?
Thanks
|
|
|
|
|
How to do it programatically ?
Microsoft Knowledge Base Article - Q230501
Any solution to "Autonumber" field running out of limits ?
Autonumber is a long integer, which will give slightly more than 2 billion numbers. If you will have more than 2 billion records, you can either use a compound key or create an unpopulated table when you reach 2 billion and start fresh.
|
|
|
|
|
I would suggest that access doesn't scale well to two billion records.
Signature space for rent. Apply by email to....
|
|
|
|
|
Jon Hulatt wrote:
I would suggest that access doesn't scale well to two billion records
Or even a couple of thousand in most cases 
Paul
|
|
|
|
|
Granted, Access doesn't scale well to 2 billion, but he intends to archive excess records and only keep N (hopefully a fairly small number) records online. I've had success with Access up to about 200K records. More than that typically causes it to blow up.
|
|
|
|
|
I am Using ADO(Visual C++) and Access 2000 database.
Trying to limit the number of records returned by the recordset with PutMaxRecords(x), and the recordset returns all records.
Thanks for your help in advance.
|
|
|
|
|
This will not work on an Access database, see here [^]. If you are still want to do something like that you could change your SELECT statement to look something like this:
<code>
"SELECT TOP 10 * FROM [myTable] WHERE user_id = 1"
</code>
Hope This Helps
Nick Parker
The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
|
|
|
|
|
|
Hi, i'm writing an app which will have its own DB that it will add information to
from searches from various databases (so they have a local copy of search results etc)
Now, which database option should i use, bearing in mind that its a stand alone app and the user could be anywhere ?
I'm told there is a free sybase db, i could use the jet engine?
erm what do people recommend?
Bryce
|
|
|
|
|
I have a SQL Server database. It is too large now.
How can I remove the transaction log without backing up the database?
In fact, I do not need any logs. Can I prevent the database from making transaction logs? Can SQL Server automaticlly remove the logs?
|
|
|
|
|
Due to architecture of sql server each database file has a transaction log file for transaction support. Support of transactions are needed for core database functionality. Therefore you can not prevent sql server making transaction logs.
Nevertheless the size of transaction log file can be controlled on database creation time with the CREATE DATABASE statement in TRANSACT-SQL. The parameter SIZE, MAXSIZE and FILEGROWTH controls initial size, maximun size and filegrowth for each database file and for log file. Setting the log file size to a fix maximum value is a recommended way not to have a filegrowth over all limits. But this needs maintenance from time to time when MAXSIZE is reached.
To reduce the size of an existent transaction log file the TRANSACT-SQL statement (not available from the sql server enterprise manager) DBCC SHRINKFILE can be used. To be secure no other user is modifying database in this time it is a good idea to set the database into the single user mode during this operation. The following are TRANSACT-SQL statements for sql server query analyzer for secure shrinking of the database log file (the log file cannot be lesser than the value given for SIZE during database create time)
USE master
EXEC sp_dboption myDatabase, 'Single User', TRUE
DBCC SHRINKFILE (myDatabaseLog, TRUNCATEONLY)
EXEC sp_dboption myDatabase, 'Single User', FALSE
GO
myDatabase is to replace with the name of the database. myDatabaseLog is to replace with name of the logical name of the logfile of the database. Alternatively to TRUNCATEONLY a target size can specified (which must be equal or greater than SIZE value on create time)
A sql server database can consist of multiple data and log files which are grouped in the same or different filegroups. If you have multiple log files you can use EMPTYFILE instead of TRUNCATEONLY to move all used pages to other log files in the same filegroup (on default the filegroup PRIMARY is used; user defined filegroup(s) can exist) to remove this or all log file except the last one needed for each database.
For details of the given TRANSACT-SQL command please use the sql server onlinehelp for reference. I Hope this helps.
|
|
|
|
|
fat888 wrote:
How can I remove the transaction log without backing up the database?
Well, now that you're in trouble, DUMP TRAN database WITH NO_LOG
but that's not a recommended procedure. On a development machine, this can be excusable. Backups are way better.
"In an organization, each person rises to the level of his own incompetence." Peter's Principle
|
|
|
|
|
Hi dear,
How can I cast between DateTime field of a table and DateTime variable from my C# code?
/////////////////////////////
DataRow drAdd;
...
drAdd["DataTime"] = System.DateTime.Now;
...
/////////////////////////////
in fact how can I fill the DateTime field of a table with current time?
Thank you in advance.
|
|
|
|
|
How can you fill a DateTime field with the current time? That depends on the database type which you don't mention. You can probably execute an internal database function such as NOW with a stored procedure. Refer to the reference manual for the particular database you are using.
|
|
|
|
|
I have an application which is accessing a SQLServer and an Oracle.
Of course, the application must know the password. I want to prevent the users from accessing the database directly. When they want to read/write data,they have to use my program.
What can I do?
Thanks.
Hi guys. I'm a very fat man. Do you like fat men?
|
|
|
|
|
The typical way of doing this is exactly what you are talking about. You have a single logon/password which uses a somewhat complicated and unique name/password convention. All other DB accounts are locked out of the DB. The user could still access the DB directly, but they would need to know the userid/password.
The other way you can do this is to encrypt the data in your DB. This is very difficult to do unless you have a library/toolkit for database access which does this for you. One way to do this is to use the digest MD5 algorithm on each and every column of data. This leads to serious problems though because it is not easy to query the DB and again, you need a library/toolkit that does the work for you.
|
|
|
|
|
Hello!
We are using ADO to work with SQLServer.
Our database is about 5Gb and some tables have about 600,000 records.
ADO provider opens such table for 3-5 minutes - the application is
"freezed".
Does anybody know how to avoid such big pause? Is it possible open table using ADO faster (I think ADO recordset retrives all records to navigate later inside it)? Some triks or tips? HELP!!!
This way we open the table:
m_pRecordset->Open((LPCSTR)"table_name", _variant_t((IDispatch*)mpdb, TRUE), adOpenKeyset, adLockOptimistic, adCmdTable);
Thank you!
Kind regards... Tau.
|
|
|
|
|
Have you tried using stored procedures rather than directly opening the table?
Michael
Programming is great. First they pay you to introduce bugs into software. Then they pay you to remove them again.
|
|
|
|
|
Can be an idea, but we would like to have more or less universal method for different servers also (Oracle, DB2, ...)
Kind regards... Tau.
|
|
|
|
|
BTW, how can I use deferred columns in ADO?
(For a deferred column, the provider is not required to retrieve data from the data store until IRowset::GetData is called for that column)
Kind regards... Tau.
|
|
|
|
|
Have you tried using adOpenForwardOnly instead of adOpenKeyset?
|
|
|
|
|
I`ve tried that - the same...
Kind regards... Tau.
|
|
|
|
|
adOpenForwardOnly, adLockReadOnly AND make sure connection and recordset have CursorLocation set to adUseServer (adUseClient will always be slower since it ignores the adOpenForwardOnly), adLockReadOnly minimizes the cursor overhead and produces a "firehose" cursor with SQL. Records will not actually be fetched until you position to them with movenext, and you can't move backwards...but should be **MUCH** faster.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
