CODEWITHSUNDEEP
sqlsql-serverms-accessvba
user1238528
user1238528

Reputation: 175

Moving from Access backend to SQL Server as be. Efficiency help needed

I am working on developing an application for my company. From the beginning we were planning on having a split DB with an access front end, and storing the back end data on our shared server. However, after doing some research we realized that storing the data in a back end access DB on a shared drive isn’t the best idea for many reasons (vpn is so slow to shared drive from remote offices, access might not be the best with millions of records, etc.). Anyways, we decided to still use the access front end, but host the data on our SQL server.

I have a couple questions about storing data on our SQL server. Right now when I insert a record I do it with something like this:

Private Sub addButton_Click()

Dim rsToRun As DAO.Recordset
Set rsToRun = CurrentDb.OpenRecordset("SELECT * FROM ToRun")

rsToRun.AddNew
rsToRun("MemNum").Value = memNumTextEntry.Value
rsToRun.Update

memNumTextEntry.Value = Null

End Sub

It seems like it is inefficient to have to use a sql statement like SELECT * FROM ToRun and then make a recordset, add to the recordset, and update it. If there are millions of records in ToRun will this take forever to run? Would it be more efficient just to use an insert statement? If so, how do you do it? Our program is still young in development so we can easily make pretty substantial changes. Nobody on my team is an access or SQL expert so any help is really appreciated.

Upvotes: 3

Views: 276

Answers (3)

Heinzi
Heinzi

Reputation: 172478

There are actually two questions in your post:

Will OpenRecordset("SELECT * FROM ToRun") immediately load all recordsets?

No. By default, DAO's OpenRecordset opens a server-side cursor, so the data is not retrieved until you actually start to move around the recordset. Still, it's bad practice to select lots of rows if you don't need to. This leads to the next question:

How should I add records in an attached SQL Server database?

There are a few ways to do that (in order of preference):

  • Use an INSERT statment. That's the most elegant and direct solution: You want to insert something, so you execute INSERT, not SELECT and AddNew. As Monty Wild explained in his answer, ADO is prefered. In particular, ADO allows you to use parameterized commands, which means that you don't have to put-into-quotes-and-escape your strings and correctly format your dates, which is not so easy to do right. (DAO also allows you to execute INSERT statements (via CurrentDb.Execute), but it does not allow you to use parameters.)

  • That said, ADO also supports the AddNew syntax familiar to you. This is a bit less elegant but requires less changes to your existing code.

  • And, finally, your old DAO code will still work. As always: If you think you have a performance problem, measure if you really have one. Clean code is great, but refactoring has a cost and it makes sense to optimize those places first where it really matters. Test, measure... then optimize.

Upvotes: 3

Christian Specht
Christian Specht

Reputation: 36441

It seems like it is inefficient to have to use a sql statement like SELECT * FROM ToRun and then make a recordset, add to the recordset, and update it. If there are millions of records in ToRun will this take forever to run?

Yes, you do need to load something from the table in order to get your Recordset, but you don't have to load any actual data.
Just add a WHERE clause to the query that doesn't return anything, like this:

    Set rsToRun = CurrentDb.OpenRecordset("SELECT * FROM ToRun WHERE 1=0")

Both INSERT statements and Recordsets have their pros and cons.

With INSERTs, you can insert many records with relatively little code, as shown in Monty Wild's answer.
On the other hand, INSERTs in the basic form shown there are prone to SQL Injection and you need to take care of "illegal" characters like ' inside your values, ideally by using parameters.

With a Recordset, you obviously need to type more code to insert a record, as shown in your question.
But in exchange, a Recordset does some of the work for you:

For example, in the line rsToRun("MemNum").Value = memNumTextEntry.Value you don't have to care about:

  • characters like ' in the input, which would break an INSERT query unless you use parameters
  • SQL Injection
  • getting the date format right when inserting date/time values

Upvotes: 2

Monty Wild
Monty Wild

Reputation: 4001

If you're working with SQL Server, use ADO. It handles server access much better than DAO.

If you are inserting data into a SQL Server table, an INSERT statement can have (in SQL 2008) up to 1000 comma-separated VALUES groups. You therefore need only one INSERT for each 1000 records. You can just append additional inserts after the first, and do your entire data transfer through one string:

INSERT INTO ToRun (MemNum) VALUES ('abc'),('def'),...,('xyz');
INSERT INTO ToRun (MemNum) VALUES ('abcd'),('efgh'),...,('wxyz');
...

You can assemble this in a string, then use an ADO Connection.Execute to do the work. It is frequently faster than multiple DAO or ADO .AddNew/.Update pairs. You just need to remember to requery your recordset afterwards if you need it to be populated with your newly-inserted data.

Upvotes: 3

Related Questions