Solved

Add values to table in MS Access?

Posted on 2004-08-17
5
695 Views
Last Modified: 2008-01-09
I need to add a few values to a table in a Microsoft Access data base. Using a TAccessApplication component I can open the database and the table with the following code:

  AccessApplication1.Connect;
  AccessApplication1.Visible := True;
  AccessApplication1.OpenCurrentDatabase(C:\MyDatabase.mdb', True);
  AccessApplication1.DoCmd.OpenTable('TableName', acNormal, acAdd);

It opens the table and I can manually add values... how do I add a record in code?

Thanks in advance!

D2.
0
Comment
Question by:wavget
5 Comments
 
LVL 11

Expert Comment

by:calinutz
ID: 11827603
using ADO query component
...
AdoQuery1.Active:=false;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('insert into table (field1,field2,...) values (value1,value2,...)');
AdoQuery1.ExecSQL;
...
This is how you insert a record into Access table

Also you can update (modify) a record using the same syntax but with different SQL string:
...
AdoQuery1.Active:=false;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('update table set field1=value1, field2=value2,... where fieldx=valuex');
AdoQuery1.ExecSQL;
...

0
 
LVL 5

Accepted Solution

by:
Darth_helge earned 125 total points
ID: 11827917
here's a quick example:
First, make an ODBC to your database.

procedure TForm1.Button1Click(Sender: TObject);
var
AdoQuery1: TAdoQuery;
Con: TAdoConnection;
begin
Con := TAdoConnection.create(nil);
Con.ConnectionString := Provider='MSDASQL.1;Persist Security Info=True;Data Source=YOUR ODBC;Initial Catalog=YOUR CATALOG';
Con.Active := true;

AdoQuery1 := TAdoQuery.Create(nil);
AdoQuery1.Connection = Con;

AdoQuery1.SQL.Text('insert into table (datefield,integerfield,stringfield) values (' + FloatToStr(MyDate)+',' + IntToStr(MyInteger) +',''' + MyString + ''')';
AdoQuery1.ExecSQL;

end;

I always treat Datevariables like a Float Value. It's much easier. When you are inserting a string, notice that you need some extra quotes.
The full sql sentence passed to your database will be like this:
insert into table (datefield,integerfield,stringfield) values ( 34545.4543 , 42 , 'This is my string' )


0
 
LVL 6

Expert Comment

by:Amir Azhdari
ID: 11828562
wavget,
try with Runsql and place your sql statement there :
AccessApplication1.DoCmd.RunSQL('insert into table (field1,field2,...) values (value1,value2,...)',false);
0
 
LVL 1

Author Comment

by:wavget
ID: 11830184
Thanks Darth_helge for your answer. It wasn't immediately obvious to me that I was supposed to change ‘table’ into the name of the table, and that ‘datefield, integerfield, stringfield’ meant the names of the fields, but after some searching for running SQL commands I figured that out. Thanks for the hint about the date variable too.
For future generations, this is what I ended up wih:

AccessApplication1.DoCmd.RunSQL('INSERT INTO TableName (T1,T2,T3) Values (25,125,-20)',false);
Where T1, T2, and T3 are field names, and 25, 125, and -20 is the data inserted into those fields.

D2.
0
 
LVL 5

Expert Comment

by:Darth_helge
ID: 11838413
I was a little fast with my datevariable...
FloatToStr works as a Datetime.
When you are working with only dates with no time (like 2004-08-07) the easiest way to insert this is by truncating the variable so it becomes an integer.
Like this:
AdoQuery1.SQL.Text('insert into table (datefield,integerfield,stringfield) values (' + IntToStr(trunc(MyDate))+',' + IntToStr(MyInteger) +',''' + MyString + ''')';
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question