Write to table

[Vsn]

Hello All,

Can some one tell me if its possible to add data to a table, in a bit of a
sameway data can be retrived by aggregate functions? And if this is possible
what the code would be to use it from VBA.

Thx, alot.


Ludovic

 

[Allen Browne]

Couple of options:
a) Execute an Append query statement.
b) OpenRecordset, and add the record.

Example of (a):
===========
Dim strSql As String
strSql = "INSERT INTO Table1 (Field1, Field2, Field3), " & _
"6 AS F1, 'Springfield' AS F2, #1/1/2006# AS F3"
dbEngine(0)(0).Execute strSql, dbFailOnError

To get the SQL statement right, you can mock up a query typing any values
in. Then change it to an Append query (Append on Query menu), and switch to
SQL view (View menu) to see the example.

Example of (b):
===========
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset("Table1")
rs.AddNew
rs!Field1 = 6
rs!Field2 = "Springfield"
rs!Field3 = #1/1/2006#
rs.Update
rs.Close
Set rs = Nothing

 

[Guest]

Allen,
Would example A work for me if I wanted to enter data from an unbound
textbox on a form to a record table?

Z

 

[Allen Browne]

Yes, you can use either method to insert records.

Using unbound forms involves extra work though, loading existing records,
keeping track of whether this is an existing record (so you need to execute
an UPDATE) or a new record (so you need to execute an INSERT), and making
sure you don't do it multiple times as an impatient user clicks repeatedly.

 

[Guest]

Allen,

Thanks very much. As I plan on having this databse be used by a large
number of folks I do want to ensure that the "impatient" users are not able
to enter multiple identical records.

Would it be advisable to setup my table and "presetting" any/all (by all I
mena the major function.) of my query and update requirements and then simply
autoforming from there?

I am very new to access and am patiently waiting for my trinining course.

Z

 

[Allen Browne]

If you are new to Access, I strongly suggest that you begin with bound forms
that do all this work for you.

You are throwing away too much when you reject all the built-in validation
that takes place at the field level (controls bound to fields know their
data type) and the record-level (e.g. the Form_BeforeUpdate event), and the
opportunity to respond to new records (Form_AfterInsert), prepare new
records (Form_BeforeInsert), respond to changes (Form_AfterUpdate), undo
operations (Form_Undo), and so on.

The bound form gives you all that and much more--we haven't started talking
about how good subforms are yet for the related data, or simply the
engine-level messages that enforce integrity.

Once you have a really good grasp on the power of bound forms, you will
rarely use unbound ones for data entry.

 

[Vsn]

Allen,

Thx for the help. This will make my application working fine.

Ludovic

 

[Vsn]

Guys,

Sorry it took me a while to get back to my workstation.

Very usfull information you gave down here. I do think I will go for the
query which indeed will give me benifits of the Access envoironment.

Thx,
Ludovic