Using SQL......???

[fenris]

I have figures out (for the most part) how to use sql statements to retrieve the records that I am looking for within my access DB. I am wondering how would one use sql to update a particular record with a unique record id, say recID and a number of fields such as: firstName,lastName,phone.

I am also interested in being able to add a new record to the database via sql statements.


Any help would be greatly appreciated...



Troy Williams B.Eng.
fenris@hotmail.com

 

[Qaroven]

Try
update <table name>
set firstName = &quot;Ben&quot;,
lastName = &quot;Helps&quot;,
phone = &quot;+61 403 395 052&quot; (assuming a text phone no)
where recID = <key value>

Also
insert into <table name>
(recID, firstName, lastName, phone)
values (1, &quot;Ben&quot;, &quot;Helps&quot;, &quot;+61 403 395 052&quot

or

insert into <table name>
select * from <other table name> where <other table criteria>
for multiple inserts
etc Ben
+61 403 395 052

 

[CarlosEscobar]

The quickest way to learn SQL is by example. Open your
database then create a new query using the Query Wizard.
Pick the tables you wish to update then choose the type
of query (Append, Delete, etc).

To answer your 1st question:

&quot;How does one update a particular record with a unique record id, say recID and a number of fields such as: firstName,lastName,phone.&quot;

Let's use a tablename of 'Customers' for our example SQL:

UPDATE Customers SET firstName = 'Joe', lastName = 'Smith', phone = '800-555-1212' WHERE recID = '00001'

To answer your 2nd question:

&quot;I am also interested in being able to add a new record to the database via sql statements.&quot;

We'll add a new customer record:

INSERT INTO Customers (recID, firstName, lastName, phone) VALUES ('00002', 'Jerry', 'Lewis', '213-555-1234')

If you made the RecID an autoNumber field then you wouldn't have to figure out what it is.. the database would do that for you.

 

[fenris]

Thank you very much for the clear and concise responses. they were exactly what I was looking for.

Thanks again....

Troy Williams B.Eng.
fenris@hotmail.com

 

[Diogo]

I would like to know how i can to obtein records of a query to a datacontrol.
I try

Private Sub Form_Load()
Dim db As Database
Dim Rs As Recordset
Set db = OpenDatabase(App.Path & &quot;\xpto.MDB&quot
Set Rs = db.OpenRecordset(&quot;SELECT cod_cli, nome FROM Tabela1;&quot
Set Data1.Recordset = Rs
End Sub

But it gives an error.
Thanks by the help.
DR

 

[CarlosEscobar]

I would have to know exactly what the error is.
If you send the project (bas frm vbp) files and
the MDB file and i reproduce your error i will
let you know the solution.

send as attachment to cescobar@accela.com
with a reminder of why you sent it.