LeTay
asked on
MS Access from Delphi
I have to access and manipulate data in a MS Access 2000 database (customer wants it)
I use Delphi XE2 and the ADO component
These datatypes are to be provided in some tables (documentation from the customer) :
date/time
true/false
If I issued an "insert into ..." command, how do I specify for example the current date, current time and true ?
Thanks
I use Delphi XE2 and the ADO component
These datatypes are to be provided in some tables (documentation from the customer) :
date/time
true/false
If I issued an "insert into ..." command, how do I specify for example the current date, current time and true ?
Thanks
ASKER
Well, I want to submit a string via a TADOCommand component..
So
var
Query:string;
begin
Query := 'insert into MyTable (DateColumn,TimeColumn,Tru eFalseColu mn) values (?,?,?)
What shall I put as values ?
So
var
Query:string;
begin
Query := 'insert into MyTable (DateColumn,TimeColumn,Tru
What shall I put as values ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I adapted the Query string like this :
insert into Session (ID,Name,Date,Time,Status, ShowInApp) values (1,'Test',Date(),Time(),0, False)
The string is then stored in a TADOCommand (CommandText property) then Execute
I get this error (in french, I translate in english) : Syntax error in instruction INSERT INTO
I join the database (table Session is empty) I changed the file extension from bws to txt because EE does not allow bws type...
AccessDB.txt
insert into Session (ID,Name,Date,Time,Status,
The string is then stored in a TADOCommand (CommandText property) then Execute
I get this error (in french, I translate in english) : Syntax error in instruction INSERT INTO
I join the database (table Session is empty) I changed the file extension from bws to txt because EE does not allow bws type...
AccessDB.txt
Try inserting hard-coded values to make sure you have the syntax correct. Once that works, you can substitute the functions.
As I said earlier, time does not exist by itself. The value of the Time field will be Dec 30, 1899 at whatever value Time() evaluates to.
Also, using the names of functions as column names can lead to problems unless you always remember to encase the reserved words in square brackets.
As I said earlier, time does not exist by itself. The value of the Time field will be Dec 30, 1899 at whatever value Time() evaluates to.
Also, using the names of functions as column names can lead to problems unless you always remember to encase the reserved words in square brackets.
ASKER
I can't modify the table layout, this is given
Can you provide another sample of what I shall write as query string ?
Can you provide another sample of what I shall write as query string ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ps : I tried the same as above, but with names like [Date] and [Time]
Same error ...
(I am not familiar with MS Access database, I generally use MS SQL and Firebird, but here I can't decide...)
Same error ...
(I am not familiar with MS Access database, I generally use MS SQL and Firebird, but here I can't decide...)
ASKER
Sinisa, I tried yours with the False as a string, but same error !
ASKER
What is strange is that the error message is "limited" to INSERT INTO
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the tables are Jet/ACEe and you are passing through the SQL, the problem fields need to be enclosed in square brackets. [Date], [Time} since Access doesn't use quotes in this way.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Great. That tells me that the query is parsed locally and not passed through so it isn't using Access syntax. But that also means that it isn't using Access SQL functions. Apparently Date() and Time() are valid in Delphi also.
ASKER
I have tried this : insert into Session (ID,Name,"Date","Time",Sta tus,ShowIn App) values (1,''Test'',Date(),0,Time( ),0)
I put this string in a TADOCommand CommandText property
The connection is correctly set to a TADOConnection previously initialised (and connected)
Error remains the same (syntax error in instruction INSERT INTO)
I think that the problem is more tricky and not (?) related to these fields but maybe a side effect of something ?
I also tried right now with a TADOQuery component as shown by Sinisa ... same error !
I put this string in a TADOCommand CommandText property
The connection is correctly set to a TADOConnection previously initialised (and connected)
Error remains the same (syntax error in instruction INSERT INTO)
I think that the problem is more tricky and not (?) related to these fields but maybe a side effect of something ?
I also tried right now with a TADOQuery component as shown by Sinisa ... same error !
Did you try simply using hard-coded values rather than functions in your Insert? I don't know what the date/time delimeter is for Delphi but for Access it is the #.
insert into Session (ID,Name,"Date","Time",Sta tus,ShowIn App) values (1,''Test'',#2/20/17#,0, #01:30:00 PM#,0)
insert into Session (ID,Name,"Date","Time",Sta
ASKER
More info
I have installed the Forge open source SQL Explorer now
I can open the Access database
I see all tables on the right panel
I can open all tables ... shows empty (normal) with column names ... EXCEPT tables Session and Section : says "unspecified error" when I try to open them.
Is the database I received corrupted ???
Or is my ODBC (installed with Windows 10 upgrade, I presume ???) not the good one ?
I have installed the Forge open source SQL Explorer now
I can open the Access database
I see all tables on the right panel
I can open all tables ... shows empty (normal) with column names ... EXCEPT tables Session and Section : says "unspecified error" when I try to open them.
Is the database I received corrupted ???
Or is my ODBC (installed with Windows 10 upgrade, I presume ???) not the good one ?
ASKER
I tried also (SQL Explorer ...) accessing a Access 97 database (same layout than the previous one, Access 2000)
Same symptoms !
Same symptoms !
Can you access a newer version of the database? MS has deprecated the A97 format.
ASKER
The database I tried first (until 10 minutes ago) is a 2000 one
ASKER
More information !
The SQL Explorer I have used is written in ... Delphi
As explained above, it issues an error (unspecified error) when trying to open two tables (the others are ok) : Session and Section.
I tried to do the same in my application and ... got the same error !
Seems now that the "guilty" is at Delphi side or somewhere between the ADO components and ODBC ???
The SQL Explorer I have used is written in ... Delphi
As explained above, it issues an error (unspecified error) when trying to open two tables (the others are ok) : Session and Section.
I tried to do the same in my application and ... got the same error !
Seems now that the "guilty" is at Delphi side or somewhere between the ADO components and ODBC ???
ASKER
Still more info
I tried the connection string with these two "Providers", both give the same error
Provider=Microsoft.Jet.OLE DB.4.0
Provider=Microsoft.ACE.OLE DB.12.0
I tried the connection string with these two "Providers", both give the same error
Provider=Microsoft.Jet.OLE
Provider=Microsoft.ACE.OLE
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...another useful info about using ms access is here ...
ASKER
many thank, will try all that this afternoon (Belgium time) and keep you informed
ASKER
I have tried the connection string you suggest
Same result
If I start with a select statement instead insert : select ID from Session where Name = 'test' ...
I get the same error as with the SQL Explorer tool (Delphi also) as when opening the Session table : unspecified error !
I am completely lost ...
Same result
If I start with a select statement instead insert : select ID from Session where Name = 'test' ...
I get the same error as with the SQL Explorer tool (Delphi also) as when opening the Session table : unspecified error !
I am completely lost ...
ASKER
I have tried this now : "select 1 from Session" : it fails (unspecified error)
It also fail if I write 'select garbage from Session' with the same error
However if I write 'select Gargabe from Clients' , I get an error explaining clearly that "Garbage" is unknown etc...
So the problem seems to be something with the table.
Its name ?
Something else in the DB ?
It also fail if I write 'select garbage from Session' with the same error
However if I write 'select Gargabe from Clients' , I get an error explaining clearly that "Garbage" is unknown etc...
So the problem seems to be something with the table.
Its name ?
Something else in the DB ?
ASKER
Hello Sinisa,
I found what the problem is !
Session and Section seem to be reserved word !
When I place them between bracket squares ... it works !
I will now test the date and time stuff (I can't change the layout of the customer tables ... so need both)
I found what the problem is !
Session and Section seem to be reserved word !
When I place them between bracket squares ... it works !
I will now test the date and time stuff (I can't change the layout of the customer tables ... so need both)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...ah sorry.. I recall that I test with duplicated table Session2 (so this is not reserved word) :-)
Just in addition, remember that in Access sql syntax, Dates and Times must be passed between # and #
For example in my applications, using access , even if dateformat is Italian I must pass dates in USA format between ##, so if I want to insert 28 august 2017 I must do just like
For example in my applications, using access , even if dateformat is Italian I must pass dates in USA format between ##, so if I want to insert 28 august 2017 I must do just like
Insert into Mytable (Mydate) values (#08/28/2017#)
Now() = current date plus time*
use True and False for best resulte
Where myFlag = True
* There is no separate time only data type. DateTime ALWAYs includes both a date and a time. The difference is that Date() populates the time with 0 so you don't normally see it. Dates are stored internally as double precision numbers with the date part being the integer and the time part being the decimal. Days are counted from 12/30/1899 so 12/31/1899 is day 1 and 12/29/1899 is day -1.