Add record only

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have an application with multi users.
The data mdb is on the server.
I want to add a record ,from the client mdb, to a table that has lot's of
records .
If i'm doing:
Set rs = db.OpenRecordset("SELECT * FROM Tbl")
It will bring all the records through the network and it's very slow.
I only need to add a record and not to edit\delete the existing records.
Is there any faster way to do it?
Is the dbAppendOnly property doing this?
 
No need for a recordset operation, just an Append query in code,
something like:

strSQL = "INSERT INTO MyTableName ( Field1, Field2 )" & _
" SELECT '" & Me.txtbox1 & "', " & Me.txtbox2
CurrentDb.Execute strSQL, dbFailOnError

The syntax above assumes Field1 is text (thus the single quotes around
the textbox reference), and Field2 is numeric. The code will work in the
form's own module (I suppose the user enters the data in a form,
right?), otherwise you will need a full reference to the form instead of
the Me. keyword.

HTH,
Nikos
 
Hi Nikos,
First, thanks for responding.
Youy right but i want to add a record with DAO and not with SQL statment.
 
Hi Roger.
I also tried this idea (but with Primarykey = -1)
but as i remember ,Access bring all the records to the client pc and then
filter the Where statment.
Is it right?
 
Yes, that is true. However the WHERE condition is tested before all of the
data is dragged across, so it significantly reduces the time. You can test
this yourself:

Sub testwhere2()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim settime As Double
Set db = CurrentDb
settime = Now
Debug.Print settime
Set rs = db.OpenRecordset("Select * from Tbl")
settime = Now
Debug.Print settime

settime = Now
Debug.Print settime
Set rs = db.OpenRecordset("Select * from Tbl where 1 = 2")
settime = Now
Debug.Print settime

MsgBox "here"

End Sub

If you look in the immediate window, you will see that the first one takes
significantly more time than the second to open.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Youy right but i want to add a record with DAO and not with SQL statment.
Click to expand...

Bearing in mind that this is a meaningless statement (I'd rather use a
chocolate biscuit to brush my teeth, but it's better practice to use a
toothbrush), the whole undertaking is based on a false premise.

Jet is a file sharing technology, not a client-server. What gets sent over
the network is as many disk pages as are required to complete any
operation. Updating a couple of integers may take several kilobytes of
data, consisting of file headers, index pages (possibly recursive), and
eventually the data pages themselves.

If your network is under strain already, then Access/ Jet poor choice
because (a) it _is_ heavy on the bandwidth; and (b) it is sensitive to
network performance and you are likely to see file corruptions. Make lots
of backups!

Sorry for the bad news


Tim F
 
Hi Roger,
Do you think that if I will use the
Set rs = db.OpenRecordset("Tbl", dbOpenDynaset , dbAppendOnly)
dbAppendOnly property in the OpenRecordset it will be the same like to use
Set rs = db.OpenRecordset("SELECT * FROM Tbl WHERE 1 = 2")
?
 
Back
Top