|
|
I am writing SQL statements for MS Access 2000 and SQL server 7.0. In MS Access 2000, Dates have to be enclosed with #, like WHERE (datefield=#10/3/2002#). But SQL Server doesn't like that. I have to use WHERE (datefield=10/3/2002). Is there any way to write the queries so they work in both programs?
BTW. I am using ADO, but not ADO.NET.
|
|
|
|
|
I don't think there is a solution. There are other conversion problems also, having to do with Access using VB functions like UCASE, which in true SQL is TOUPPER (at least, I don't think SQL server handles UCASE, but even if it does, I believe my point is true non-the-less).
My recommendation is to either implement a pre-parser that converts from Access syntax to SQL server syntax or maintain two dictionaries of SQL statements.
Maybe someone else can shed some light on this.
Marc
|
|
|
|
|
The following works with ODBC, not sure about ADO.
select * from table where datefield={d 'yyyy-mm-dd'}
Dave.
|
|
|
|
|
Thanks for the tip. That works in SQL server but not Access. Access thinks {} is a GUID.
|
|
|
|
|
Hi,
I have two tables t1 and t2 and they look like this:
t1:
t1_id | t1_title | t1_content |...
t2:
t2_id | t2_language | t2_t1_id |...
where t2_t1_id is id from the first table. Now if I want to select elements from both tables Im using sometnihg like this:
SELECT * FROM t1, t2 WHERE t1_id = t2_t1_id AND [condition]
and i have nice results  ... but my question is how can I delete rows
with only one query  ...
so I do not want to use this:
DELETE FROM t1 WHERE [condition]
DELETE FROM t2 WHERE [condition]
but I would like to do something like:
DELETE FROM t1, t2 WHERE t1_id = t2_t1_id AND [condition]
is this possible and how?... please post some examples or links
|
|
|
|
|
You need to set up a foreign key with cascading deletes. t1_id is the primary key, t2_t1_id is a foriegn key referencing table t1, field t1_id. In the foreign key you can tell it that whenever you delete rows in t1, to "cascade" those deletes to any rows in t2 that reference the deleted rows in t1. Then, all you do is "delete from t1 where...".
Hope that helps! BTW, this is why it's nice to use an abstract key like an ID, instead of a meaningful field, say, a part number. Assume your primary key was a part number. If the user wants to change the part number, you'd have to go through a lot of consternation when the foreign key association breaks. This is why there's now something called "cascade updates" too! (us old timers, ha, ha, ha) didn't have that capability in older databases.
Marc
|
|
|
|
|
How can I setup runtime ADO with minimal files / registration.
I would like to distribute the runtime files with my application. What files are needed ?
Thanks
|
|
|
|
|
I'm fairly sure M$ license agreement for ado means you need to redistibute the whole lot, unchanged, in the installer from their site.
Signature space for rent. Apply by email to....
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
