Appending Data with ADODB 3

[dbero]

Good morning;

I am updating a table from a form using an ADO recordset. the table is linked to the open database. All I am doing is taking values from a form, and loading them to the table, and closing.

I am using this;
Dim AddAddress As Recordset
Set AddAddress = New ADODB.Recordset


Addtransaction.Open "Select * from TaskTable",CurrentProject.Connection, adOpenKeyset, adLockOptimistic
AddAddress.AddNew
AddAddress!User_ID = UserName
etc....


Rather than using the select * to initialize, is there a way to open a dataset for append only and not select any records?
Thank you very much,

 

[RoyVidar]

Execute on the connection - say on local db (assuming User_ID is a string)

[tt]dim cn as adodb.connection
set cn = currentproject.connection
cn.Execute "INSERT INTO TaskTable (User_ID) " & _
"Values ('" & UserName & "')"[/tt]

Roy-Vidar

 

[dbero]

thank you. i was able to get a single value inserted, but how do i string together multiple fields into a single record?

 

[lespaul]

the structure is:

INSERT INTO TableName (Field1, Field2, Field3) VALUES (Value1, Value2, Value3)

you would just have to make sure you get all the ' " & around the applicable fields.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[RoyVidar]

Reading again - you are updating a table in the current database with a form - the ordinary way of updating, would be to use bound form, and allow Access to do the job, and not program it.

Else, if you are operating unbound, then it's all about building an action query, then fire it off.

[tt]dim strSql as string
strSql = "INSERT INTO mytable (field1, field2, field3) " & _
"values ('" & strMyText & "', " & lngMyNumber & _
", #" & format$(dtMyDate, "yyyy-mm-dd") & "#)"[/tt]

remembering that text fields need text delimiters (single quote), and should it contain single quotes, you might need to take it a step further

[tt]...values ('" & replace(strMyText, "'", "''") & "'...[/tt]

Date fields needs octothorpe (#) as delimiter, and an unambiguous format. Number fields no delimiter, though you might run into a bit of fun if you're using comma as decimalseparator where you live.

Roy-Vidar

 

[PHV]

is there a way to open a dataset for append only and not select any records
Addtransaction.Open "SELECT * FROM TaskTable[!] WHERE 1=0[/!]",CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886