Solved

Object Variable or With block variable not set

Posted on 2013-11-20
18
1,042 Views
Last Modified: 2014-01-13
Hello~
When trying to execute this block of code below I am getting the Run-time error '91':
Object Variable or With block variable not set. Tried to change it to Provider=Microsoft.Ace.OLEDB.12.0;" & _ and that gave the same error. Anyone have any suggestions?
Thank you!



Private Sub ServerDBConnection()
    ' Procedure is used to create the connection to the server database
    Dim conn As ADODB.Connection
    Dim sPath As String
    Dim sPW As String
    
    
    sPath = "\\fr01apthr001\HRData\HRIS\App\pcdata.accdb"
    sPW = "jb007"
    
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sPath & ";" & _
        "Jet OLEDB:Database Password=" & sPW
    conn.Open
    
    '  display the last process time
    Me.txtLastTime.Value = sysProcessTime(sysProcess, Now(), "sysRetrieve")
End Sub

Open in new window

0
Comment
Question by:dminx13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
  • +1
18 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 39662938
dminx,

You have to initialize the conn variable. Just change your variable declaration to
Dim conn As New ADODB.Connection

Open in new window

Matt
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39662951
I should also mention that if you don't want to initialize it there, you can always do it separately:
Set conn = New ADODB.Connection

Open in new window

As long as that is above where you're using it (when setting the connection string) you'll be fine.
0
 

Author Comment

by:dminx13
ID: 39663136
OK that worked but now it's throwing the same error on a different place.  This process is supposed to delete existing tables and recreate them with the newest information. It doesn't appear like it is picking up the external data that it needs to in the SQLStatement it is just empty....

Set rs = ServerDB.OpenRecordset(SQLStatement) the SQLStatement is "JCC" which is a table in the database that this process needs to update.


Here is the full code with the line that is offending:
Private Function ExportRecords(SQLStatement As String, ExternalRecords As Recordset) As Long
    '  Procedure is used to export the JCC to the PeopleCenter Database
    Dim db As Database
    Dim rs As Recordset
    Dim sSQL As String
    Dim sProg As String
    Dim fld As DAO.Field
    Dim i As Long
    
    
    '  Look for data in external databases and create the recordset as needed
    Select Case SQLStatement
        Case "Applicants"                           '  Applicant database information
            Set rs = CreateApplicantDBRecordset(SQLStatement)
        Case "Hires"                                '  Applicant DB information
            Set rs = CreateApplicantDBRecordset(SQLStatement)
        Case "Requisitions"                         '  Requisition records
            Set rs = CreateRequisitionRecordset(SQLStatement)
        Case "Contract_Staff"
            Set rs = CreateContractRecordset(SQLStatement)
        '  Used to be for contract positions - replaced with actual positions
        'Case "Positions"
        '    Set rs = CreateContractRecordset(SQLStatement)
        Case Else   '  Recordset can be found in the Server database
            Set rs = ServerDB.OpenRecordset(SQLStatement)    '<--------
    End Select
    
    i = 0
    With rs
        If .EOF And .BOF Then
            '  do nothing
        Else
            .MoveLast
            .MoveFirst
            Do
                i = i + 1
                sProg = "Exporting " + CStr(i) + " of " + CStr(.RecordCount) + " records"
                Me.txtCurrent.Value = sProg
                DoEvents
                    ExternalRecords.AddNew
                        For Each fld In ExternalRecords.Fields
                            If IsNull(.Fields(fld.Name)) Then
                                '  Do Nothing
                            Else
                                ExternalRecords.Fields(fld.Name) = .Fields(fld.Name)
                            End If
                        Next fld
                    ExternalRecords.Update
                .MoveNext
            Loop Until .EOF
            
        End If
    End With
    
    ExportRecords = i
    Set rs = Nothing
    Set db = Nothing
End Function

Open in new window

Option-Compare-Database.docx
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 35

Expert Comment

by:mvidas
ID: 39663168
Where does the ServerDB variable come from? I don't see it dimensioned or passed at all.
0
 

Author Comment

by:dminx13
ID: 39663346
It is in the Option Compare section as well as the ServerDBConnection code in the word document that I attached.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39663378
I see now. It looks like in the subroutine ServerDBConnection you have this line commented out:
    'Set ServerDB = wsp.OpenDatabase(sPath, False, False, sPW)

Open in new window

This was the line that initialized the variable and sets it to reference your server database. Why did you comment it out?
0
 

Author Comment

by:dminx13
ID: 39663408
Good question.  Under what section is that? I know that when we updated from 2003 some of the wsp lines went away because of the conn lines.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39663430
It was in the original Sub you posted about, just commented out (I assumed you removed that before posting on EE for easier readability). Line 23 below:
Private Sub ServerDBConnection()
    ' Procedure is used to create the connection to the server database
    'Dim wsp As Workspace
    Dim conn As New ADODB.Connection
    Dim sPath As String
    Dim sPW As String
    
    
    ''''sPath = GetSystemData("datafile")
    ''''sPW = ";PWD=" + GetSystemData("password")
    sPath = "\\fr01apthr001\HRData\HRIS\App\pcdata.accdb"
    sPW = "jb007"
    
    conn.ConnectionString = _
        "Provider=Microsoft.Ace.OLEDB.12.0;" & _
        "Data Source=" & sPath & ";" & _
        "Jet OLEDB:Database Password=" & sPW
    conn.Open
    'sPath = "I:\Users\HRD\HRIS\App\TestDB\pcdata.mdb"
    'sPW = "jb007"
    
    'Set wsp = DBEngine.Workspaces(0)
    'Set ServerDB = wsp.OpenDatabase(sPath, False, False, sPW)
    
    '  display the last process time
    Me.txtLastTime.Value = sysProcessTime(sysProcess, Now(), "sysRetrieve")
End Sub

Open in new window

If you're not going to go that route, maybe in the function "ExportRecords" where you're getting the error, you could use something like
 Set rs = New Recordset
 rs.Open SQLStatement, conn, adOpenKeyset, adLockReadOnly

Open in new window

Or however you want to use it. Since it seems you're converting all your old methods into ADODB, that would be one way to do it. I am not a database expert, so its possible there are better methods than this.
0
 

Author Comment

by:dminx13
ID: 39664464
We were only trying to convert what we HAD to. That is why the dim wsp was commented out as well as the other line. I tried adding those back in and that created different errors. Will get back into this more tomorrow and try what you've suggested.
0
 

Author Comment

by:dminx13
ID: 39709291
Not sure what to do with this post at this point. I don't have the expertise to traslate into code some of the suggestions and at this point I just linked tables into the database that I could not update as a workaround. So I can get the data that I need, just not using what was suggested. I suppose I can let this expire as some of the posts above may be a solution, I just can't translate into viable code to test them.
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39709349
I've asked for assistance from some other experts here who can hopefully help you transition it better. Stay patient please :)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39709506
You should not use a recordset object to execute (Delete, Insert, or Update) statements.  Use any of the following:
* dbEngine(0)(0).Execute()
* your connection object
* DoCmd.RunQuery (or similar command)
* CurrentDb.Execute()
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39709542
If you have limited coding experience, consider hiring someone, such as Top Experts you can see on the right side of the page. Many have their contact information in their profiles and will be happy to help on very reasonable terms. I've been on both sides and can almost guarantee that you won't be sorry.
0
 

Author Comment

by:dminx13
ID: 39712034
aikimark:
Can you be more specific? Like replace this line of code with any of these lines of code and try.

vadimrapp1:
I'd love to but we don't have the money. We just spent $$ on a contractor for a limited engagement to convert the database and we had only 200 hours. So at this point I am on my own to complete what the contractor did not :-(
0
 

Accepted Solution

by:
dminx13 earned 0 total points
ID: 39712101
So in starting fresh and re-coping the code from the original 2003 database it worked.......

So you saw the above nightmare. This is what works:

Private Sub ServerDBConnection()
    ' Procedure is used to create the connection to the server database
    Dim wsp As Workspace
    Dim sPath As String
    Dim sPW As String
    
    
    sPath = GetSystemData("datafile")
    sPW = ";PWD=" + GetSystemData("password")
    
    Set wsp = DBEngine.Workspaces(0)
    Set ServerDB = wsp.OpenDatabase(sPath, False, False, sPW)
    
    '  display the last process time
    Me.txtLastTime.Value = sysProcessTime(sysProcess, Now(), "sysRetrieve")
End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39712283
Can you be more specific?
Rather than iterating one recordset and appending its fields to a row in another recordset, you should write an append query to directly transfer the data from one table to another.  You would probably start with the existing (Select) queries and rewrite them as (Insert) append queries.

You can use the IN keyword to point to the \\fr01apthr001\HRData\HRIS\App\pcdata.accdb database.  Alternatively, you could attach the tables in the target/external database.

The point is probably moot, since you seem to have a solution to your problem.
0
 

Author Closing Comment

by:dminx13
ID: 39776125
Code was re-written for 2010 by a contractor. Did not work but after puttingback to original 2003 code it worked fine. No assistance needed to correct at this point
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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