?
Solved

INSERT Statement using VBA in Access Database

Posted on 2007-07-20
7
Medium Priority
?
4,979 Views
Last Modified: 2013-11-27
I am trying to insert a bunch of dummy records into an Access Database table using VBA in a module called survey_results. Below is the code I am using. I'm getting the infamous Object variable or With block variable not set (Error 91). I have Access 2003 using windows xp.

Public Sub build_survey()


'Option Compare Database
Dim random_number As Integer
Dim dbs As DAO.Database
Dim upperbound As Integer
Dim lowerbound As Integer
Dim i As Integer
Dim j As Integer
Dim k as Integer

lowerbound = 1
upperbound = 5

For i = 1 To 666 'Number of Classes
    For j = 1 To 500 'Number of Students answering the survey
        For k = 1 To 10 'Number of Question
             'random_number = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
             random_number = Int((5 * Rnd) + 1) 'Values can be from 1 to 5
            dbs.Execute "INSERT INTO survey_results (class_id, student_id, question_id, value_id) VALUES (i,j,k,random_number)"
     

        Next
    Next
Next
End Sub
0
Comment
Question by:mickygene
7 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 100 total points
ID: 19537164
add this line after all of the Dim statements:
Set dbs = CurrentDB
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 19537167
Dim dbs As DAO.Database  ' You don't need this

Instead of:

dbs.Execute "INSERT INTO survey_results (class_id, student_id, question_id, value_id) VALUES

Use

CurrentDb.Execute "INSERT INTO survey_results (class_id, student_id, question_id, value_id) VALUES
0
 
LVL 75
ID: 19537169
mbiz ... it's past your bed time ..?

mx
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 13

Assisted Solution

by:John Mc Hale
John Mc Hale earned 100 total points
ID: 19537299
'Option Compare Database
Dim random_number As Integer
Dim dbs As DAO.Database
Dim upperbound As Integer
Dim lowerbound As Integer
Dim i As Integer
Dim j As Integer
Dim k as Integer

lowerbound = 1
upperbound = 5

For i = 1 To 666 'Number of Classes
    For j = 1 To 500 'Number of Students answering the survey
        For k = 1 To 10 'Number of Question
             'random_number = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
             random_number = Int((5 * Rnd) + 1) 'Values can be from 1 to 5
            CurrentDB().Execute "INSERT INTO survey_results (class_id, student_id, question_id, value_id) VALUES (" & i & "," & j & "," & k & "," & random_number &")"
     

        Next
    Next
Next
End Sub
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 19537974
As an alternative:


Rank: Guru
fredthered:
'Option Compare Database
Dim random_number As Integer
Dim upperbound As Integer
Dim lowerbound As Integer
Dim i As Integer
Dim j As Integer
Dim k as Integer

lowerbound = 1
upperbound = 5

For i = 1 To 666 'Number of Classes
    For j = 1 To 500 'Number of Students answering the survey
        For k = 1 To 10 'Number of Question
             'random_number = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
             random_number = Int((5 * Rnd) + 1) 'Values can be from 1 to 5
            CurrentProject.Connection.Execute "INSERT INTO survey_results (class_id, student_id, question_id, value_id) VALUES (" & i & "," & j & "," & k & "," & random_number &")"
     

        Next
    Next
Next
End Sub

The CurrentProject.Connection points to the ADO connection in use for the database ... you can use it just as you would an ADO connection object you build.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 100 total points
ID: 19538226
Hi all.

For what it's worth - Miriam's first answer not only addresses the question, but will be the most efficient method.
Bear in mind that this is an iterative process - and using CurrentDb in each iteration of the Loop requires the object's creation each time.
Considering that it will be executed many times - I'd say that would add up to a noticable effect on the process.

And while many often overstate the advantage DAO has over ADO (it's not as profund - or even always the case -as is often thought) the DAO CurrentDb should have a noticable edge over the CurrentProject.Connection.Execute.

They'll all *work* though.  Just saying that as they're effectively equivalent the first is also the most expedient.

However - has everyone considered that it'll be over 3 million records inserted? :-S
Into what?  A Jet table?  Possible - but depends what else you have going on in the application.
3,330,000 executions - 3 and a third million inserts?  The third alone would be a slog.
All together it *will* take aaaaages.  (If it actually completes :-S).
0
 
LVL 75
ID: 19539916
Yes ... I did completely miss the iteration part ... it was a very quick code scan, saw not Set for dbs object var ... and plugged in CurrentDb.  But you are certainly correct.  And with a possible 3meg inserts .. oh yeah

Actually maybe we can get the poster to try it all 3 ways ... just to see the time differences ... for academic reasons.

 mickygene .... can you do that for us and post back the results here, since you have the dataset?

thx.mx
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question