devxlogo

Opening a Recordset for Adding Records

Opening a Recordset for Adding Records

Question:
Which is the fastest way to open a recordset (ADO) to add new records:

SqlString="select * from authors where id<0"rs.open SqlString, Connection, adOpenDynamic, 
adLockPessimistic, adcmdTextrs.Add .....

or

rs.open "authors", Connection, adOpenDynamic, 
adLockPessimistic, adcmdTable

Answer:
The approach you should take really depends on your architecture, but I would advise against using the second statement, which returns all the records in a table. If you don’t need them then there’s no point in returning them. Otherwise, it might be best to limit the number of records returned to 100 or 250.

You’re on the right track with the first query. An alternate method is to create a stored procedure called GetEmptyTable and pass it the table name. Then you can use the same stored procedure for different tables throughout your code.

GetEmptyTable 'Categories'CREATE PROCEDURE GetEmptyTable	@TableName  varchar(255)ASSET NOCOUNT ONDECLARE @strSQL varchar(255)	IF @TableName IS NOT NULL OR @TableName <> ''		BEGIN			SELECT @strSQL = 'SELECT * FROM ' +
@TableName + ' WHERE 1 = 2' EXECUTE (@strSQL) END

I would definitely recommend using stored procedures instead of sending dynamic SQL to the server. There’s a number of reasons why it’s better but the bottom line is stored procedures are faster.

See also  How to Avoid Money Transfer Scams  
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist