dminx13
asked on
Object Variable or With block variable not set
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.OLE DB.12.0;" & _ and that gave the same error. Anyone have any suggestions?
Thank you!
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.OLE
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
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
As long as that is above where you're using it (when setting the connection string) you'll be fine.
ASKER
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(SQL Statement) 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:
Set rs = ServerDB.OpenRecordset(SQL
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
Option-Compare-Database.docx
Where does the ServerDB variable come from? I don't see it dimensioned or passed at all.
ASKER
It is in the Option Compare section as well as the ServerDBConnection code in the word document that I attached.
I see now. It looks like in the subroutine ServerDBConnection you have this line commented out:
'Set ServerDB = wsp.OpenDatabase(sPath, False, False, sPW)
This was the line that initialized the variable and sets it to reference your server database. Why did you comment it out?
ASKER
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.
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
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
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.
ASKER
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.
ASKER
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.
I've asked for assistance from some other experts here who can hopefully help you transition it better. Stay patient please :)
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()
* dbEngine(0)(0).Execute()
* your connection object
* DoCmd.RunQuery (or similar command)
* CurrentDb.Execute()
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.
ASKER
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 :-(
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 :-(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
The point is probably moot, since you seem to have a solution to your problem.
ASKER
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
You have to initialize the conn variable. Just change your variable declaration to
Open in new window
Matt