Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

ERROR: Operation is not allowed when the object is closed

Hi

Im using code#1 for getting connection to the datadase and retrieve recordset(RecSet). It works fine ,But when the connection is not there A run time error (Operation is not allowed when the object is closed)shows in code#2, from where code#1 is being called (marked as "---->>")
I have googled many times and tried all the codes to check for empty record set (I think the problem is due to that ). But my problem is not yet solved.

Please help me in doing this.

-----------------------------------------------------------------------
code #1
---------
Public Function GetItemCodeConnection(ByVal sqlqry As String) As ADODB.Recordset
On Error GoTo Cleanup
Set ConOra = New ADODB.Connection
If ConOra.State = ObjectStateEnum.adStateOpen Then
ConOra.Close
End If
ConString = "myconnectiomstring"
ConOra.ConnectionString = ConString
ConOra.Open
Dim ra As Long
Set RecSet = Nothing
Set RecSet = ConOra.Execute(sqlqry, ra, CommandTypeEnum.adCmdText)
Set GetItemCodeConnection = RecSet
Exit Function
Application.ScreenUpdating = False
Cleanup:
Debug.Print Err.Description
Set RecSet = Nothing
If ConOra.State = ObjectStateEnum.adStateOpen Then
ConOra.Close
End If
Worksheets("Sheet1").Range("ExitVariable").value = 0
End Function

==============================
code#2
-------
Public Function GetMatchQuery(ValueString As String, TableString As String, FieldString As String) As Integer
MatchString = "Select " & FieldString & " from " & TableString & " where " & FieldString & "='" & ValueString & "'"
Set RecSet = Nothing
Set RecSet = GetItemCodeConnection(MatchString)
---->>If RecSet.EOF And RecSet.BOF Then
MatchResult = 0
Else
MatchResult = 1
End If
GetMatchQuery = MatchResult
End Function


Post your answer or comment

comments powered by Disqus
Please help

whenever im running this code, sometimes its work fine.........but at sometime it throws an error message that "
Operation is not allowed when the object is closed."

what should i do....anybody please help.

Dim ConOra As New ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim ConString As String
Public RecordCount As Integer
Dim Tempstring As String
Sub Startconnection(Sqlqry As String)

If ConOra.State = ObjectStateEnum.adStateOpen Then
ConOra.Close
End If
ConString = "Provider=MSDAORA.1;User ID=excel;Password=excel;Data Source=dev;Persist Security Info=False"
ConOra.ConnectionString = ConString
ConOra.Open
Dim ra As Long
If RecSet.State = adStateOpen Then RecSet.Close
Set RecSet = Nothing
Set RecSet = New ADODB.Recordset
RecSet.Open Sqlqry, ConOra, adOpenKeyset, adLockOptimistic
ConOra.Close
End Sub
Sub LoadItemDetails(sheet As Worksheet, StartRange As String, CopyRange As String, Sqlqry As String)
Startconnection (Sqlqry)
On Error GoTo Cleanup
Application.ScreenUpdating = False
sheet.Activate
sheet.Range(StartRange).Select
For Each x In RecSet.Fields
ActiveCell.Value = x.Name
ActiveCell.Offset(0, 1).Select
Next
Call sheet.Range(CopyRange).CopyFromRecordset(RecSet)

In SPOTRATE sheet there's two macro, actually the same just import different section from database (I'm sure both are existed and active!), however "update 01" macro works all right, but "update 02" doesn't.

Actually "update 02" successfully connected with database, whereas import nothing, with error msg box "Requeste: exec ps_GET_PNL '20120208','20120209','5J9' Operation is not allowed when the object is closed".

I attach the excel file here, anyone can help is highly appreciated!!
Thanks very much!

hi
i wrote a macro to connect to a database using ADO
to retrieve some records

Private Sub Display()
Dim sql As String
Dim con As ADODB.Connection
Dim rs1 As ADODB.recordset

sql = "select * from table"
MsgBox sql

Set con = New ADODB.Connection
Set rs1 = New ADODB.recordset
con.Open "DSN=" & DSN & ";Database=" & DB & ";UID=" _
& User & ";PWD=" & Password
rs1.Open sql, con

If Not rs1.EOF Then <<---debugger points here..
MsgBox "Yes"
Else
MsgBox "No"
End If
rs1.Close
con.Close
End Sub

Whenever i run this, i would get error "Operation is not allowed when
the object is closed." and the debugger will point to the statement "If
Not rs1.EOF Then...". I tried using "Do Until rs1.EOF" but the error is
the same..
Can anyone advise me on what is wrong..thanks very much

A access Form Called an SqlServer stored Procedure
The stored procedure returns a recordset and a return value, How to get the recordset and the returned value.
The code like follows just get the RecordCount = -1 all the time.

	VB:
	
 test() 
    Dim cn2 As New ADODB.Connection 
    Dim cmd     As New ADODB.Command 
    Dim rs3 As New ADODB.Recordset 
     
    Set cn2 = New ADODB.Connection 
    Set rs3 = New ADODB.Recordset 
    Set cmd = New ADODB.Command 
     
    constr = "provider=sqloledb;server=shaxappxa;database=abcde_Test;uid=abcdef_Test;pwd=Passwordaa" 
    cn2.Open constr 
     
    cmd.ActiveConnection = cn2 
    cmd.CommandType = adCmdStoredProc 
    cmd.CommandText = "usp_transfer_fromTempCustomer_2012" 
    cmd.Parameters.Refresh 
    cmd.Parameters("@User") = "AccessTo" 
    cmd.Parameters("@theYear") = selectYear 
    cmd.Parameters("@thePeriod") = selectMonth 
    cmd.Parameters("@type") = ImportD 
    Set rs3 = cmd.Execute() 
    Debug.Print rs3.RecordCount 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When The Import=0 the stored procedure return value 0,The Import=1 the stored procedure return value -1 and a datatable,How can I get all the recordset ??
And there is anyother porblem, many times I run the Vba,It takes the '3704' error "Operation is not allowed when the object is closed"......what's the problem?

Im not sure If I need to post this question elsewhere, but here goes...

From excel 2002, I recorded a macro to add a shape, then editing the macro
code, the visual basic editor comes up. I clicked on "Microsoft Visual Basic
Help"
I then type in a keyword "shape" in box 1, then double clicking "shape" in
box 2 to bring up a topic list causes an error. Slecting any other topics
from box3 causes the same error. I still get the help text for the selection
in the right window for what ever I select... but the error is annoying..

A runtime error has occured
Do you wish to debug?

Line 114
Error: Operation is not allowed when the object is closed

Whats going on with this?

--
Regards,
John

I have a Stored procedure as the sQRY in the thread below, this a changed statement to bring back more results. The original Exec Statement is also below. The problem I'm having is with the 4th section of the Exec

My problem is that once I run the code it brings back this error message, I dont quite understand what it means, can someone explain to me where I gone wrong as the Exec Statement works as it should in SQL Server

Error : Operation is not allowed when the object is closed. 3704

Original Code
New Code
Sheet1.Range("E4:F9").ClearContents
    Set rsDW = New ADODB.Recordset
    sQRY = "DECLARE @wlvals varchar(100) " & _
                "SET @wlvals =  char(39) + 'T2DEA' + char(39) + ',' + char(39) +  'T2DEP' + char(39) " & _
                "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals"
    rsDW.CursorLocation = adUseClient
    rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
    Application.ScreenUpdating = False
    Sheet1.Range("E4").CopyFromRecordset rsDW
    rsDW.Close
    Set rsDW = Nothing
I cant firgure out where I have gone wrong, as it works great in SQL Server...

I have raised a thread in
http://www.dbforums.com/showthread.php?t=1634012
but had no joy just yet...

Hey Folks,

While coding in VBA, I had been receiving script errors when attempting to utilize the online help from within the VBA IDE.

After fiddling with security settings and much searching for KB Articles as well as Googling newsgroups archives all to no avail, I finally discovered the resolution to this issue.

From within Internet Explorer, under "Tools" and "Internet Options" and "General", run the "Delete Files" under the "Temporary Internet Files" section.

For those of you who have ever experienced the Internet Explorer problem whereby "View" and "Source" does nothing, the above should resolve that issue as well.

Cheers,

TC

Hi,

I'm trying to filter my PivotTable value field to show only those values
that are greater than a certain number. I'm getting the following error:
"arbitrary shape is not allowed when its elements cross a reference
dimension"

Can someone shed some lights as to what that error means?

Thanks.

Hi,

I'm trying to filter my PivotTable value field to show only those values
that are greater than a certain number. I'm getting the following error:
"arbitrary shape is not allowed when its elements cross a reference
dimension"

Can someone shed some lights as to what that error means?

Thanks.

I'm getting the following error message:

Run-time error '3704' Operation is not allowed when the object is closed.

This code previously worked before but I'm using a much more complex sql query which involves constructing a table before returning the final table.

I'm fairly new to using ADO and sql so please be gentle. I've tried to search for a solution but couldn't find anything relevant.

Many thanks!

Here is my code:


	VB:
	
 DownloadData() 
    Dim sedol As String 
    Dim iNoPeriod As Integer 
    Dim adoRs As ADODB.Recordset 
    Dim adoComm As ADODB.Command 
    Dim arrDates(100) As Variant 
    Dim iCtr As Integer 
    Dim dAlphaDate As Date 
    Dim dReturnDate As Date 
    Dim sSql As String 
    Dim portID As Integer 
     
    sedol = Range("SEDOL").Value 
    portID = Range("PORT_ID").Value 
     
    Set adoComm = Nothing 
    Set adoRs = Nothing 
    Set adoComm = New ADODB.Command 
    Set adoRs = New ADODB.Recordset 
     
     
     ' Find the location to paste data
    Sheets("hist").Select 
    Range("startCell").Select 
     
     ' clear data
    Range(Selection, Selection.End(xlDown)).Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.ClearContents 
     
    Range("startCell").Select 
     
    With adoComm 
        .ActiveConnection = ConnectToGevs 
        .CommandType = adCmdText 
        .CommandText = SQLStatement(dAlphaDate, dReturnDate, sedol, portID) 
        Set adoRs = .Execute 
         
    End With 
     
    ActiveCell.CopyFromRecordset adoRs 
     
     
    Set adoRs = Nothing 
    Set adoComm = Nothing 
     
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi i am getting an error when i try to read the info in a recordset that is coming from a store procedure, the error is:

"Operation is not allowed when the object is close". what can be. I am trying to check if the name of the recordset is not EOF.

Thanks,
Andres.

Hi,

I get the following error when trying to run a VBA code from excel.
----------- Error Start -----------
Run-time error '3704':
Operation is not allowed when the object is closed.
----------- Error End -----------

I'll put the code here and explain a bit more after that.
----------- Code Start -----------
Public Sub UpdateTable1()

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset

With Worksheets("Compare")
objCmd.CommandText = "USE " & ComboBox1.Text & _
" select name from dbo.sysobjects " & _
"where OBJECTPROPERTY(id, N'IsUserTable') = 1 " & _
"and name 'dtproperties'"
objCmd.CommandType = adCmdText

Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

Set objRs = objCmd.Execute

Do While Not objRs.EOF
ComboBox3.AddItem (objRs(0))
objRs.MoveNext
Loop
End With

End Sub
----------- Code End -----------

That code works if I use some simpler query.
I've checked with SQL Profiler and the query comes to the
SQL Server as it should be. I've ran the query in Query Analyzer
and it works there.

I would really appreciate any help on this.

----
mkarja

I have a routine that runs a sql query for each cell in the range. I am running three different instances of it for 3 different ranges.The first two instances have simple sql queries and work fine (no variables declared)

The third instance uses a more complex query and I get an error:

Run-time error '3704':

Operation is not allowed when the object is closed.

Has anyone got any pointers on how to get to the bottom of the cause here.

The routine is below:

    Dim cnn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rstSQL As New ADODB.Recordset
    Dim sqlQRY As String
    Dim sqlCN As String
    Dim sCusName As String
    Dim j As Long
    Dim i As Long
    Dim sDateRange As String
    Dim sSCode As String
    Dim lngcolCount As Long
    Dim lngRowCount As Long
    
    'lngcolCount = Range("C3", Range("C3").End(xlToRight)).Count
    lngcolCount = Cells(3, Columns.Count).End(xlToLeft).Column 'change from romperstomper
    'lngRowCount = Range("B5", Range("B5").End(xlDown)).Count
    lngRowCount = 11
     sCusName = Cells(1, 3).Value
     ' MsgBox sCusName
   
   
For j = 9 To lngRowCount
        
  For i = 3 To lngcolCount
       ' MsgBox lngcolCount
        sDateRng = Cells(4, i).Value
        sSCode = Cells(j, 1).Value
        sqlCN = "Provider=SQLOLEDB; Server=B-SQL; Database=B-LIVE; Trusted_Connection=yes;"
        cnn.Open sqlCN
        sqlQRY = "SELECT       SUM ([Sales Amount (Actual)]) As 'Summed Amount'" & _
        "FROM [b-live].[dbo].[ACME$Value Entry]" & _
        "Join [b-live].[dbo].[ACME$Default Dimension]" & _
        "ON [ACME$Default Dimension].[No_] = [ACME$Value Entry].[Item No_]" & _
        "WHERE [ACME$Default Dimension].[Dimension Value Code] = '" & sSCode & "' " & _
        "AND [ACME$Value Entry].[Posting Date] BETWEEN  " & sDateRng & "  " & _
        "AND [ACME$Value Entry].[Source No_] = '" & sCusName & "'"
        
        ' sqlQRY = "SELECT TOP 5000  Sum ([ACME$Value Entry].[Invoiced Quantity]) As 'Summed Amount'" & _
         '"FROM [b-live].[dbo].[ACME$Customer]," & _
         '"      [b-live].[dbo].[ACME$Item]," & _
         '"[b-live].[dbo].[ACME$Item Budget Entry]," & _
         '"[b-live].[dbo].[ACME$Value Entry]" & _
         '"WHERE [ACME$Customer].[No_]= '" & sCusName & "'" & _
         '"AND [ACME$Item Budget Entry].[Budget Dimension 1 Code] = '" & sSCode & "' " & _
         '"AND [ACME$Value Entry].[Source No_] = [ACME$Customer].[No_]" & _
         '"AND [ACME$Value Entry].[Item No_] = [ACME$Item].[No_]" & _
         '"AND [ACME$Item Budget Entry].[Item No_] = [ACME$Item].[No_] " & _
         '"AND [ACME$Value Entry].[Posting Date] BETWEEN   " & sDateRng & "  ;" ' SET DATEFORMAT
mdy
            rstSQL.Open cnn.Execute(sqlQRY)
            'rstSQL.Open sqlQRY, cnn.Execute
            Cells(j, i).CopyFromRecordset rstSQL
            rstSQL.Close
            cnn.Close
    Next i
    
Next j


Query compiles in sql just fine, but when I run the vba sub, I receive this error msg
Run-time error '3704':
Operation is not allowed when the object is closed And as you'll notice, I have included SET NOCOUNT ON in the sSQL string. Can anyone help me with this?

Option Explicit


Public Function ADOCn() As String
'ADODB connection string
    ADOCn = "PROVIDER=SQLOLEDB;DATA SOURCE=MyServer;INITIAL CATALOG=MyInitCatalog;UID=someguy;PWD=somepassword;"
End Function

Sub Refresh_Query()

Dim adoRS As ADODB.Recordset
Dim sSQL As String
Dim ListRow As Integer
Dim Start_Date As String
Dim End_Date As String
Dim lastrow As Long

Start_Date = Sheet1.Range("Start_Date").Text
End_Date = Sheet1.Range("End_Date").Text


Set adoRS = New ADODB.Recordset

sSQL = " use Somecat"
sSQL = sSQL & vbCrLf & " Declare @startdate datetime"
sSQL = sSQL & vbCrLf & " Declare @enddate datetime"
sSQL = sSQL & vbCrLf & " Set @startdate ='" & Start_Date & "'"
sSQL = sSQL & vbCrLf & " Set @enddate ='" & End_Date & "'"
sSQL = sSQL & vbCrLf & " SET NOCOUNT ON"
sSQL = sSQL & vbCrLf & " Select * Into #Mytemptable From"
sSQL = sSQL & vbCrLf & " (Select * From MyTable Where mydate between @startdate and @enddate) as tmp1"
sSQL = sSQL & vbCrLf & " IF OBJECT_ID(N'tempdb..#Mytemptable', N'U') IS NOT NULL"
sSQL = sSQL & vbCrLf & " DROP TABLE #Mytemptable;"

adoRS.Open sSQL, ADOCn

Sheet1.Range("A11").CopyFromRecordset adoRS
adoRS.Close

lastrow = Sheet1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheet1.Rows("11:" & lastrow).RowHeight = 12.75

MsgBox "Information has been updated", vbOKOnly, "Updated"


Hi,

I get the following error when trying to run a VBA code from excel.
----------- Error Start -----------
Run-time error '3704':
Operation is not allowed when the object is closed.
----------- Error End -----------

I'll put the code here and explain a bit more after that.
----------- Code Start -----------
Public Sub UpdateTable1()

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset

With Worksheets("Compare")
objCmd.CommandText = "USE " & ComboBox1.Text & _
" select name from dbo.sysobjects " & _
"where OBJECTPROPERTY(id, N'IsUserTable') = 1 " & _
"and name <> 'dtproperties'"
objCmd.CommandType = adCmdText

Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

Set objRs = objCmd.Execute

Do While Not objRs.EOF
ComboBox3.AddItem (objRs(0))
objRs.MoveNext
Loop
End With

End Sub
----------- Code End -----------

That code works if I use some simpler query.
I've checked with SQL Profiler and the query comes to the
SQL Server as it should be. I've ran the query in Query Analyzer
and it works there.

I would really appreciate any help on this.

----
mkarja

hello! i'm just new in here.. I started practicing vba programming since yesterday... pls help me on my problem...

I've already connected my records at ms access.. the macro i made is only runnable at 1st row of my table.. so i made a loop to fill up the next rows with data..

but i got these error..

Run-time error '3705':
Operation is not allowed when the object is open

here's my code:
________________________________________
Sub PriceListComputation()

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Dim row As Integer

con.ConnectionString = "DBQ=C:PriceList.mdb; " & "DRIVER={Microsoft Access Driver (*.mdb)}"

con.Open

Set rs.ActiveConnection = con

row = ActiveSheet.UsedRange.Rows.Count

For moveRow = 7 To (row - 22)       'here's the loop i made where i got error.

rs.Open "SELECT * FROM Uniforms WHERE item_name = '" & ActiveSheet.Cells (moveRow, 2) & "'"

Next

startRow = 7          'this is the loop on the first row of my table, but didn't work at next
Do Until rs.EOF
    Cells(startRow, 2) = rs.Fields(2).Value
    Cells(startRow, 7) = rs.Fields(3).Value
    Cells(startRow, 6) = rs.Fields(5).Value
    rs.MoveNext
    startRow = startRow + 1
Loop

rs.Close
Set rs = Nothing

con.Close
Set con = Nothing

End Sub
_________________________________________

I got the logic but i don't know how to code it properly..

still got many questions to ask.. but my priority was this one.. help me plz.. thanks in advance

I'm encountering problems when trying to retrieve a count from an SQL stored procedure (sp). The sp has an if statement that checks a couple of things before finally inserting a record. At the end, regardless of what happensn, there is a SELECT statement that returns the number of records in the table. The sp is shortened as follows:

CREATE PROCEDURE [dbo].[insert_AP]
/*variables declared here*/
AS

/* ensure that a file exists */
if( (/*count for a record*/) = 1)
BEGIN
IF( (/*variables are within proper range*/ ) )
BEGIN
/*insert new record*/
END
END
/*count statement*/
GO

Syntactically, the sp is fine and performs the insert properly. However, when I try to get the results of the count statement at the end with .getString, I get an error message saying that "Operation is not allowed when the object is closed." When I run this in SQL Query Analyzer, the count is returned, so I don't exactly understand if this is a T-SQL problem or a VBA problem. Can anyone tell me how I can get that count statement at the end? Thanks in advance!

My code, if anyone is interested, is as follows:


	VB:
	
 AddAP 
    On Error Goto ExitAddAP 
    Dim cmd As ADODB.Command 
    Dim con As ADODB.Connection 
    Dim rs  As ADODB.Recordset 
     
    Set con = New ADODB.Connection 
    Set cmd = New ADODB.Command 
    Set rs = New ADODB.Recordset 
     
    con.Open ConnectionString 
    cmd.ActiveConnection = con 
    cmd.CommandText = SP_AddAP '

i am extracting data from sql server 2008 using excel vba.the extracted data should be in between two dates which is entered by the user in two text boxes.the code is belo.when i am running the code,i get a error like " operation is not allowed when the object is closed".

Hi guys,

I have this code I have been using to help me fetch data from my ERP database...

I updated it to get Max(Desired_Qty) instead of straight Desired_Qty

I am getting an error

Run-Time error 3704: Operation is not allowed when the object is closed and errors out at

 in code below

Sub GetMaxQty()

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String
Dim vIDs1 As Variant, vIDs2 As Variant, vIDs3 As Variant
Dim strIDs1 As String, strIDs2 As String, strIDs3 As String
Dim c As Range
Dim rngIDs As Range
Dim LastRow As Long
Dim i As Integer
Dim FR As Long: FR = 2                                      'first row of ID
Dim LR As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row     'last row of ID
Dim R_i As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

'Establish Database Connection
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

db_name = "SANDBOX_ODBC"
UserName = "ME"
Password = "PASSWORD"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" & Password + ";"
rsOra.CursorLocation = adUseServer

'iterate records in 1000s and update
With Sheets(1)
    For R_i = FR To LR Step 1000
        If R_i + 1000 > LR Then
    'set rngids
    Set rngIDs = .Range(Cells(R_i, 1), Cells(R_i, 1).End(xlDown))
Else
    Set rngIDs = .Range(Cells(R_i, 1), Cells(R_i + 999, 1))
End If
        'create array of values in range being processed
        vIDs1 = rngIDs.Value
        'create SQL InStr from vIDs1 array
        strIDs1 = "'" & Join(Application.WorksheetFunction.Transpose(vIDs1), "','") &
"'"
        'retrieve data from db
        rsOra.Open "SELECT Max(DESIRED_QTY) FROM WORK_ORDER Where (PART_ID In(" & strIDs1 & "))
", cnOra, adOpenStatic
        'now process recordset and return MAX QTY to correct ID

        For Each c In rngIDs
    With rsOra
        .Find "PART_ID = '" & c.Value & "'"
        If Not .EOF Then
            c.Offset(0, 2).Value = rsOra![Max(DESIRED_QTY)] 'Insert Product Code in column BJ
        End If
        .MoveFirst
    End With
Next c
        'clear rngIDs
        Set rngIDs = Nothing
        rsOra.Close
    Next R_i
End With

'Close Connections...Forgetting to close your connection will sometimes result in Dr.Watsons
'rsOra.Close
cnOra.Close
Set rsOra = Nothing

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
What I had before was working... this only gave a random quantity

Sub GetMaxQty()

'Defining variables
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim db_name As String
Dim UserName As String
Dim Password As String
Dim vIDs1 As Variant, vIDs2 As Variant, vIDs3 As Variant
Dim strIDs1 As String, strIDs2 As String, strIDs3 As String
Dim c As Range
Dim rngIDs As Range
Dim LastRow As Long
Dim i As Integer
Dim FR As Long: FR = 2                                      'first row of ID
Dim LR As Long: LR = Cells(Rows.Count, 1).End(xlUp).Row     'last row of ID
Dim R_i As Long

Application.ScreenUpdating = False
Application.EnableEvents = False

'Establish Database Connection
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset

db_name = "SANDBOX_ODBC"
UserName = "ME"
Password = "PASSWORD"

'Making an ODBC connection according to ADO
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" & Password + ";"
rsOra.CursorLocation = adUseServer

'iterate records in 1000s and update
With Sheets(1)
    For R_i = FR To LR Step 1000
        If R_i + 1000 > LR Then
    'set rngids
    Set rngIDs = .Range(Cells(R_i, 1), Cells(R_i, 1).End(xlDown))
Else
    Set rngIDs = .Range(Cells(R_i, 1), Cells(R_i + 999, 1))
End If
        'create array of values in range being processed
        vIDs1 = rngIDs.Value
        'create SQL InStr from vIDs1 array
        strIDs1 = "'" & Join(Application.WorksheetFunction.Transpose(vIDs1), "','") &
"'"
        'retrieve data from db
        rsOra.Open "SELECT PART_ID, DESIRED_QTY FROM WORK_ORDER Where (PART_ID In(" & strIDs1 & "))
", cnOra, adOpenStatic
        'now process recordset and return MAX QTY to correct ID

        For Each c In rngIDs
    With rsOra
        .Find "PART_ID = '" & c.Value & "'"
        If Not .EOF Then
            c.Offset(0, 2).Value = rsOra![DESIRED_QTY] 'Insert Product Code in column BJ
        End If
        .MoveFirst
    End With
Next c
        'clear rngIDs
        Set rngIDs = Nothing
        rsOra.Close
    Next R_i
End With

'Close Connections...Forgetting to close your connection will sometimes result in Dr.Watsons
'rsOra.Close
cnOra.Close
Set rsOra = Nothing

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

I think the problem is that I need to "Select" the PART_ID too in the first code but the SQL won't let me...

It gives me Ora error ORA-00937: not a single-group group function Does anyone have an idea? I want to Select the PART_ID and the MAX(DESIRED_QTY) from the Table at the same time, basically, I think.

I tried adding GROUP BY PART_ID to end of SELECT statement.. and still another SQL error: 3265: item cannot be found in the collection corresponding to...

modification is not allowed becouse the document is locked can anyone help me
Thank you

Hello. I get the message "Reference is not Valid" when the following piece
of code executes from my program; k is an integer, focallength is a double,
and sheetname is a string:

sheetname = "r32-" & k + 1 & "-" & FocalLength

Dim infourierrng As Range
Dim outfourierrng As Range

Set infourierrng = Worksheets(sheetname).Range("J15:J1038")
Set outfourierrng = Worksheets(sheetname).Range("L15:L1038")

Application.Run "ATPVBAEN.XLA!Fourier", infourierrng, outfourierrng, False,
False

This message appears in a messagebox; if I click OK, everything proceeds
fine, and the fourier transform works, so it isn't really much of a problem,
except I don't want this message to appear anytime anyone runs this macro.
Any help would be greatly appreciated.

When using the following in access 2003, I’m ok with:

Dim cnn As ADODB.Connection
Dim arcv As New ADODB.Recordset
Set cnn = CurrentProject.Connection
Set arcv = New ADODB.Recordset

But with the same references checked and using access 97, I get:

“run-time error 3251; Operation is not supported for this type of object”

????

thank you...

Hi,

I have an XLAM (Add-in) which opens a workbook, works upon it and saves it
with SaveAs in the same format as that of the original workbook opened. I get
this error with message "The specified dimension is not valid for the current
chart type" at randomly when the SaveAs operation is called.

Any idea why am I getting this error at SaveAs as I believe the error has
got something to do with Charts/Graphs.

Any information on this would be appreciated.

Thanks in advance.

I posted this as an Access question and have received no
responses, so I have decided that maybe this is more of
an Excel programming question. I am hesitant to duplicate
the posting, but I am stumped!

I have an application that has an Excel front-end where
some of the behind the scenes stuff happens in Access.
The user only sees Excel. Up to this point, data is
input and read into simple Access tables.

The latest revision requires some rather complex Access
queries. I have gotten everything working in the queries
as required, but now I need to get them into Excel using
VBA code.

The first thing I have to do is two "Make Table"
Queries. From these two tables, I then run another more
complex query. The make table queries are required every
time this procedure runs.

At this point, I am trying to output the new table to an
Excel Spreadsheet, just to make sure it is working.

The code is:

Set rst2 = New ADODB.Recordset

Src = "SELECT Materials.*, IIf(IsNull
([Materials.Color]),'~',[Materials.Color]) AS ColorMat
INTO tblMaterials FROM Materials;"

With rst2
.Open Source:=Src, ActiveConnection:=cnn
Set WDF = Worksheets("Delivery Forecast")
WDF.Range("A1").Offset().CopyFromRecordset rst2
End With

When I do this I get an error that says "Operation is not
allowed when Object is Closed."

The connection to Access seems fine, or I would not be
able to run the query. The error occurs when I try to
copy rst2 into Excel. Since the worksheet WDF cannot be
the object that the error is referring to, it must be the
recordset "rst2." I am assuming this is happening
because the table is new. How do I open this new table,
so I can copy the recordset? After the procedure has
run, how do I delete the new table?

Thanks,
Laurie


No luck finding an answer? You could always try Google.