|
I create every day ACCESS file ,
by copynig from initial, rename and that write,
Every day.
but initial file takes size of 210 kilobytes,
by the year 60 Megabytes. It is much.
How to make smaller initial acces file ?
By excluding system tables?(How to do it?)
Compressing?
By excluding unneed fuctionality?
I use OLE DB templates.
and JET ACCESS 4.0 driver.
Thanks you.
|
|
|
|
|
Hi !
I'm using an Access 2000 database through ODBC, using only standard SQL requests. In my table, I have an autoincrement field. After adding a new record, I need to know the value of the autoincrement field. To do that, I get the record which has the higher number in the autoincrement field (because it is the last added one), and read this field value.
This works fine in a monouser environnement. But, if two records are added almost simultaneously, there is a risk that the last value of the autoincrement field is not the value of the record I just added. To solve this, I tried to use those SQL commands :
BEGIN TRANSACTION
COMMIT TRANSACTION
But it seems that Access does not handle these SQL commands.
How would you solve my problem ?
Thank you for your help
Jerome
|
|
|
|
|
Switch to a proper database (tee hee!)
But seriously - Access doesn't support transactions at all (AFAIK), but can be used as a front end to SQLServer, and therefore MSDE (free SQL Server desktop engine). You may be able to cobble together something using that sort of rig, but will probably dstruggle using Access alone
Of course, my personal favourite would be bin Access completely and use Oracle (or eve MySQL)
Apparently the problem is caused by Access not actually being a relational database, but rather an ISAM db with relational addons
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Actually, it does support transactions - I've written apps which depend on it in my pre-SQLServer days. Don't let your bias for Oracle show quite so much.
I'd ask whether or not stuff like autocommit is turned off (there's an SQLSetConnectAttr setting).
Steve S
|
|
|
|
|
Hello All,
I have some data in the Excel file, and i want to get it into sql table.
How can i do that? Can i use macro ? if Yes than how should i use the macro?
Thanking in advance.
Prasad.
|
|
|
|
|
The easiest way to transfer data accross is to create a DTS package: start SQL Enterprise Manager, go to the Data Transformation Services node, right click on "Local Packages" and select "New package".
By drag and drop, you can create 2 connections objects, 1 Excel object and 1 SQL Server object
(you can create other types of connections as well, including ODBC, csv file, Oracle, Sybase, etc...)
Then drag and drop a "Transform Data Task" object, set its source to Excel and its target to SQLServer.
Right-click on the "Transform Data Task" object and select properties. You can configure how you want the data to be mapped, it even creates a table in SQL Server if you want. It is quite easy to use, virtually no code to write.
Save your package, execute it and you're done. If you want to re-use this package it is quite handy. You can add all sorts of other tasks/macro that run before or after the data pump. You can also specify the order in which each step should be executed.
 Chris
|
|
|
|
|
This is beyond me....
Look at the photo of my view please...
https://www.readysecure2.com/users/polajenkocom/images/combine.jpg
I want to get all records with a CreatedDate that is within the past 7 days. Can anyone help me do this?
Thanks
|
|
|
|
|
try adding DateDiff(dd,CreatedDate,GetDate()) <= 7 to your where clause.
onwards and upwards...
|
|
|
|
|
I got it with
SELECT 'Announcement' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate, URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Announcements
LEFT OUTER JOIN
Users ON Announcements.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Document' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Documents
LEFT OUTER JOIN
Users ON Documents.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Links' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Links
LEFT OUTER JOIN
Users ON Links.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
ORDER BY CreatedDate DESC
|
|
|
|
|
Using a DateAdd of 0 is a waste. All you need is one DateDiff. Your where clause should look like this:
where DateDiff(dd,CreatedDate,GetDate()) <= 7
to show rows created in the past 7 days.
onwards and upwards...
|
|
|
|
|
I’m trying to extract the PK’s and FK’s of a table given it’s name.
I can get as far as confirming that a table has a PK by obtaining that PK’s entry in the sysindexes table, but now my problem is that I can’t seem to figure out what column in that table actually represents the PK.
In the sysindexes table it merely specifies that this is a primary key and this is the parent table, but not which column in that table.
What I really want, given a table name is:
myprimaryKeyColumnName[s]
myForeignKeyColumnName[s] & it's associated Table[s]
btw, I know it's in the sys* tables, but I'm just missing something and don't have my systables poster at home. 
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG picture :: my first abstract photo
|
|
|
|
|
Try sp_fkeys (tablename) to display the foriegn keys. Or, if you want to dig it out the hard way, try something like:
select
Object_Name(fkeyid) + '.' + Col_Name(fkeyid,fkey),
keyno
from
sysforeignkeys
where
rkeyid = Object_ID('YourPrimaryTableName')
order by
1, 2
onwards and upwards...
|
|
|
|
|
Try using ADO's Connection.OpenSchema method - haven't checked but think will give you the nec. info
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
|
I am using SQL Server 2000. I ran into a situation where I needed to increment a revision. The revision could be up to two letters in length. The code below works great. This is just for an example to anyone who may need it.
Check This out:
declare @r varchar(2)
declare @MaxRevLetter INT
declare @seriesLetter1 VARCHAR(1)
declare @seriesLetter2 VARCHAR(1)
declare @NewRevision VARCHAR(2)
-- SET Revision Letter(s) to Check
SET @r = 'CX'
SET @MaxRevLetter = ASCII('X')
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@r) > 0 AND LEN(@r) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@r != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@r))) <> @MaxRevLetter BEGIN
SET @NewRevision = CHAR(ASCII(@r)+1)
END
END
ELSE BEGIN
SET @NewRevision = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@r,0,LEN(@r) )
SET @seriesLetter2 = SUBSTRING(@r,2,LEN(@r) )
-- because we know that if there is a Z the letter must be set and can not be
-- incremented. We need to test for a Z.
IF (@seriesLetter1 + @seriesLetter2 != 'ZZ' AND
@seriesLetter1 + @seriesLetter2 != 'ZX' AND
@seriesLetter1 + @seriesLetter2 != 'XZ' AND
@seriesLetter1 + @seriesLetter2 != 'XX') BEGIN
-- Ok if the first letter is NOT an X and the second letter is not an X
-- increment the second letter so AB would be AC
IF (ASCII(@seriesLetter2) <> @MaxRevLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
ELSE BEGIN
-- ok the second letter was an X Ex: AX so we need to increment the first letter
-- and set the second letter back. AX would be BA
SET @seriesLetter1 = CHAR(ASCII(@seriesLetter1)+1)
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
END
ELSE BEGIN
-- ok there is some combo of X and Z in the string. so set the value
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET NewRevision = 'The Revision XX is the last revision possable.'
END
END
END
enjoy.
Let me know if you have any questions
Will
|
|
|
|
|
The following line is not correct:
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = @seriesLetter1 = 'The Revision XX is the last revision possable.'
END
please change it to
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = 'The Revision XX is the last revision possable.'
END
Will
|
|
|
|
|
I've been using MySQL for some months which has a build-in "md5()" function for md5 excryption, very convenient. Now I need to do something uses SQL server 2000 instead of MySQL, I found that there's no such function or similar feature in SQL server 2000... Hmm then how do you encrypt user passwords? Do you have to develop your own DLL to do the encryption(which would suck)?
|
|
|
|
|
Do it on the client side. For unmanaged code, look at the CryptoAPI documentation; for managed code, there's the System.Security.Cryptography.MD5 class.
You might be able to use OPENQUERY and an extended stored procedure, but then we're heavily in the realms of suckiness. 
|
|
|
|
|
Hi
I'm trying to save a mp3 files in a table in access file.
SO I'm using ADO. I can save and read data in access file if it is of the tipes (Long , String, BOOL .) But I don't know how to Read & Write BIG array.
I think that I should use VARIANT but I did not know how!
Thanks very much for any kind of help
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
I am now interested in SQL / database programming and I would like to find and learn about sql. Does anyone know some good compilers maybe free?? also a website or book about sql / database programming. I have 1 year in visual c++ 6 and about 1/2 a year in c++(you know in console...).
I would appreciate it if anyone replied. 
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
SQL is a generic term for a language used to comunicate with a database. That language can vary from various SQL databases. There is no "compiler" as there is in C++. Choose your database software and then learn the associated SQL syntax that goes with the database software.
Some Free database software:
----------------------------
MSDE (Microsoft Data Engine)
MySQL
PostgreSQL
Some Non-Free software:
-----------------------
Microsoft SQL Server
Oracle
DB/2
Once you've chosen your database software that you'll need to search online for some SQL primers. There are tons of sites which will cover the basics of SQL programming.
|
|
|
|
|
I have found SQL Team[^] a valuable resource, especially when you start to get into more complex SQL statements.
Hope this helps!
Jeremy Oldham
|
|
|
|
|
 THANKS ALOT!!!
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
How can I add PrimaryKey to my DataTable in dataset in Runtime!
I have >>
<xs:key name="SignKey1"><br />
<xs:selector xpath="." /><br />
<xs:field xpath="mstns:PrsId" /><br />
</xs:key>
in my Dataset?! but it gives error that I dont have PrimaryKey! Isnt it PrimaryKey?!?!
Always,
Hovik.
|
|
|
|
|
Just starting to play around with database access in dotNET. I've run through a number of tutorials, and reviewed a number of articles on this web site. All was well and good until I tried to do something on my own... Uh Oh...
I created a simple relational database with the following tables...
Table - PhoneNumber
PhoneNumber_ID
PhoneNumber
PhoneNumberTypeID
PersonID
Table - PhoneNumberType
PhoneNumberType_ID
PhoneNumberType
Table - Person
Person_ID
FirstName
LastName
I threw some data in the PhoneNumberType and Person tables, and then wanted to see if I could create a form to add entries to the PhoneNumber table.
I added two comboboxes to the form, and bound them to datasets filled with the contents of PhoneNumberType and Person tables. The valuemember of the comboboxes is the ***_ID column of the selected row in the combobox. That's working fine.
I added a textbox to the form for the phone number.
So, I have all the elements necessary to add a new row to the PhoneNumber table, but I'm not sure of the most efficient method of performing the insert. After poking around on the internet for a while, it looks like there might be two options avaliable to me (there are probably more)...
1) Create a DataAdapter for the PhoneNumber table, then create and fill a dataset with all the phone numbers in the PhoneNumber table. Add the new row and then update the database with the new dataset (need to figure out how to do that). This seems like overkill for this simple function.
2) Create an SQLCommand and execute it with the new row values. I vaguely remember seeing an article that discussed using ?'s in the SQL command, and then filling in the parameters afterwards, but I can't seem to find it again.
Is either of these the preferred approach, or is there another approach I might consider.
Thanks... whew that was a mouthful.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
