.AddNew without opening entire tbl in recordset 2

[perplexd]

Hi all
The aim I have is to add a new record to a single table. However the way I am doing it at the moment means I am opening the entire table in a recordset and then updating...I'm guessing this is not particularly efficient.

Is there a better way to add/append a new record to the end of an existing table? Many thanks.

 

[sunaj]

Hi,

The ADO way is to open the entire table:
Rst.Open "tblMyTable", YourConnection, adOpenKeyset, adLockOptimistic, adCmdTable

But you can also use the Connection,execute:
t= 10
YourConnection.Execute "INSERT INTO tblMyTable(MyField) VALUES(" & cstr(t) & &quot"

Which one is most efficent depends on the circumtances. You'll find that method 1 is actually faster when inserting a lot of records. Depending on which database you use, there might be alternatives. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jantie78]

Hi,

You can write your the SQL statement for the update yourself!

Dim conn As New ADODB.Connection
Dim sql As String

conn.ConnectionString = "..."

sql = "INSERT INTO myTable (Field1, Field2) VALUES ('val1','val2')"

conn.Execute(sql)

conn.Close

Greetz,
Jan If this response was usefull to you, please mark it with a Star!

 

[perplexd]

Cheers Guys!

Its a little database, so the maximum I will ever generate at a once is 99...so should I go for the SQL or open the rs?

 

[jantie78]

I always use SQL, this is always faster! If this response was usefull to you, please mark it with a Star!

 

[sunaj]

With only 99 inserts, I would say that it is a matter of taste. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jantie78]

When you open a RecordSet and you use a clientside cursor with a large table, it can take a long time just to open the recordset.

I think just the inserting is as fast with a recordset than sql. But opening a recordset can take some time... If this response was usefull to you, please mark it with a Star!

 

[sunaj]

jantie,
That is not correct.
If you are doing a lot of insert is is MUCH faster to open the recordset (once), add all the data (.addnew) and then update the database (.update) than using .execute with a insert statement for each insert (you'll have to communicate with the database for each row).
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[jantie78]

In that case you are right! If this response was usefull to you, please mark it with a Star!

 

[perplexd]

Sorry to pester you...but it I think I made a silly error somewhere in the code...could you have a quick look?

Dim product As String
Dim ItemType As String
Dim sql As String
Dim ProdID As String
Dim ProdType As String

'On Error GoTo Error


If optFileCode.Value = True Then
ProdID = txtFileCode.Text
product = txtFileCode.Text
'Set Boolean field to True
ProdType = "-1"
ItemType = "File Code"
Else
ProdID = txtPartNo.Text
product = txtPartNo.Text
'Set Boolean field to False
ProdType = "0"
ItemType = "Part Number"
End If

con.Execute ("INSERT INTO tblReference (ProdID, ProdDesc, ProdType) " & _
"VALUES (" & ProdID & ", " & cboDesc.Text & ", " & ProdType & &quot&quot


Every time I try to run it, it says "No value given for one or more required parameters". Why is this?

 

[jantie78]

Forgot some quotes I think...

con.Execute ("INSERT INTO tblReference (ProdID, ProdDesc, ProdType) " & _
"VALUES ('" & ProdID & "', '" & cboDesc.Text & "', '" & ProdType & "')&quot
If this response was usefull to you, please mark it with a Star!

 

[sunaj]

What error do you get?
If ProdDesc is a text field, you need to include single quotes:

con.Execute ("INSERT INTO tblReference (ProdID, ProdDesc, ProdType) " & _
"VALUES (" & ProdID & ",'" & cboDesc.Text & "', " & ProdType & &quot&quot

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[lovell811]

Strings also need single quotes, so if you couldn't tell that is why jantie78 had all those quotes so try his and see what happens. ----------------
Joe

 

[perplexd]

Cheers guys...i knew it was right infront of me!!

 

[ewgewg]

I will not discuss what is faster, open a recordset or use a SQl-Command insert.

But when you will use a recordset for AddNew, you can open your table with
myRecordSet.Open "Select * from myTable where 1 = 0", .....

For ADO now all necessary informations are given for the recordset and the .AddNew and you avoid a long time to open the recordset by large tables.



'Sorry, my english isn't the best. I speak naturally bavarian.