Method Open the object _Recordset Failed


i got a problem said that "Method Open the object _Recordset Failed"
what should i do - -'


Sponsored Links:



Hello,
***The text in the SQL is case sensitive, the field names in my table were lower case, in the VBA SQL each were capitalized***

Please help! I am trying to export data using file system object and receive the following error:

Run-time error '-2147467259 (80004005)'"
Method 'Open' of object '_Recordset' Failed

The routine writes a blank .txt file to my desktop. When debugging, it fails rs.Open line. The query is valid and works in query window.

References selected: Visual Basic For Applications, Microsoft Access 11.0 Object Library, OLE Automation, Microsoft DAO 3.6 Object Library, Microsoft ActiveX Data Objects 2.1 Library, Microsoft Scripting Runtime

MS Access Version: 2003 Professional Edition (11.6566.8117) SP2

The "NASDAQ" table:
date open close 2006-01-03 10718 10809 2005-12-01 10806 10717 2005-11-01 10569 10440


Code: Sub XPORT() Dim fso As FileSystemObject Dim txt As TextStream Dim rs As ADODB.Recordset Dim s As String Set fso = New FileSystemObject Set txt = fso.CreateTextFile("C:Documents and Settingsxjp1pyuDesktopxportit.txt", True) Set rs = New ADODB.Recordset rs.ActiveConnection = CurrentProject.Connection rs.Open "SELECT Date, Open, Close FROM NASDAQ", , adOpenForwardOnly, adLockReadOnly txt.WriteLine "Date, Open, Close" Do While Not rs.EOF s = """" & FormatDateTime(rs("Date"), vbShortDate) & """, " s = s & FormatNumber(rs("Open"), 2, vbFalse, vbFalse, vbFalse) & ", " s = s & FormatNumber(rs("Close"), 2, vbFalse, vbFalse, vbFalse) txt.WriteLine s rs.MoveNext Loop rs.Close txt.Close End Sub




I have been having quite a fight this morning with accessing a table in my FE DB via an ADO Recordset object.

I am working on creating cfg tables in both the client and server databases. Next up was to fetch the application version from each table. Then to compare and make sure the client and server code matches. Seems simple enough.

So my code for the server config table works perfectly / flawlessly. Using that I capture both the run-time domain (DEV / QA / PROD) and also the version string.

Next I built a class based on the class accessing the server table. Adjusted the SQL to be correct for the local FE table. The query string executes perfectly in a query window, but not so when run from an ADO object in VBA.

Da code...


	Code:
	'This API searches for the KeyValue based on the KeyName and Run-Time Domain
Public Function LocateKeyValue(keyname As String) As String
On Error GoTo Err_LocateKeyValue

  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  'Define attachment to database table specifics
  Set adoRS = New ADODB.Recordset
  adoRS.ActiveConnection = CurrentProject.Connection
  adoRS.Source = "cfg"
  adoRS.CursorType = adOpenDynamic
  adoRS.LockType = adLockPessimistic

  'Define a query to look for the KeyValue based on the KeyName and Run-Time Domain
  strSQL = "SELECT cfg.keyvalue " & _
    "FROM cfg " & _
    "WHERE (((cfg.keyname)='" & keyname & "') AND ((cfg.domain)='" & domain & "'));"

  'Open query results
  adoRS.Open strSQL

  'Was no record found?
  If adoRS.BOF Or adoRS.EOF Then
    LocateKeyValue = ""
  Else
    'Fetch the values found
    keyvalue = Nz(adoRS!keyvalue, 0)
    'And return the fetched keyvalue
    LocateKeyValue = keyvalue
  End If

  'Close the database table
  adoRS.Close

Exit_LocateKeyValue:
  'Clean up the connection to the database
  Set adoRS = Nothing

  Exit Function

Err_LocateKeyValue:
  Call errorhandler_MsgBox("Class: clsObjCfgTbl, Function: LocateKeyValue()")
  LocateKeyValue = ""
  Resume Exit_LocateKeyValue

End Function

At first I was getting this sort of error:


	Code:
	Date: 20110823 Time: 10:49:07 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Automation error
Unspecified error

So I did a bit of searching for that, came across a MS document telling how to get additional information about such errors:

http://support.microsoft.com/kb/186063

So I added that to my error handler. However, not much help for the effort:


	Code:
	Date: 20110823 Time: 11:29:40 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Automation error
Unspecified error 
MessageText: Unspecified error

Since adding that code, I have also received this type of error... which is the type the code presently throws.


	Code:
	Date: 20110823 Time: 11:42:24 UserID: c_mlueck
Error Source: Class: clsObjCfgTbl, Function: LocateKeyValue()
Error Number: -2147467259
Error Description: Method 'Open' of object '_Recordset' failed
MessageText: Unspecified error

I have seen references that a reserved word has been used, which causes the error only when being run from VBA. No keywords jump out at me. Do you see one?

And pretty much the same query runs on the server... though that is a different ADO connection, which is doing pass-through queries.

Suggestions please??!?!?! Thanks!




I am getting this error message "Method 'Form' of object '_SubForm' failed", when my code On Current runs (see code below), when I click on cmdGrowth (which opens another form-Growth) and when I click on the close button for the Growth form. Prior to today this functioned fine. I was making some coding changes today, but not anything having to do with this section of code or the cmdGrowth button (at least as far as I am aware-I got kind of deep into it, so I am not able to completely retrace my tracks). I cannot find reference to this error message anywhere. Can someone please help me make sense of the error message and maybe look at my code to see if there is something there that I am missing? Is it possible my Growth form got corrupted somehow in what I was doing? Any ideas would be appreciated!

Thank you,
Kristen

Private Sub Form_Current()
On Error GoTo Form_Current_Err

'When ClientInfo is not a new record, run the following code
If Me.NewRecord = False Then
' Synchs FindName and current record

Forms!frmClientInfo!cboFindName = Forms!frmClientInfo!ClientID

'If Program = Pregnant Woman, cmdGrowth.enable = false
'and DueDate.visible = true

If Me!frmEnrollment.Form!Program = "Pregnant Woman" Then
Me!cmdGrowth.Enabled = False
Me!DueDate.Visible = True

'Otherwise cmdGrowth.enable = true and DueDate.visible = false

Else
Me!cmdGrowth.Enabled = True
Me!DueDate.Visible = False
End If

End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub




I need some assistance......I am getting this Run-Time Error Method 'ItemData of Object' _ListBox' Failed what I am doing wrong? Here is a copy of my code and also should the code be under Sub Form1 or Option Compare Database/Option Explicit? Again I am new to this world of VBA.Here is my code...Any Assistance would be greatly appericated.

Private Sub Form_Current()
Dim oItem As Variant
Dim oItem1 As Variant
Dim bFound As Boolean
Dim sTemp As String
Dim sTemp1 As String
Dim sValue As String
Dim sChar As String
Dim iCount As Integer
Dim iCount1 As Integer
Dim iListItemsCount As Long
Dim iListItemsCount1 As Long

sTemp = Nz(Me!ErrorCodeDescriptionList88.Value, ";")
sTemp1 = Nz(Me!ErrorCodesandCorrections.Value, ";")
iListItemsCount = 0
iListItemsCount1 = 0
bFound = False
iCount = 0
iCount1 = 0
Call ClearListBox

For iCount = 1 To Len(sTemp) + 1
sChar = Mid(sTemp, iCount, 1)
If StrComp(sChar, ";") = 0 Or iCount = Len(sTemp) + 1 Then
bFound = False
Do
If StrComp(Trim(Me!ErrorCodeDescriptionList88.ItemDat a(iListItemsCount)), Trim(sValue)) = 0 Then
Me!ErrorCodeDescriptionList88.Selected(iListItemsC ount) = True
bFound = True
End If
iListItemsCount = iListItemsCount + 1
Loop Until bFound = True Or iListItemsCount = Me!ErrorCodeDescriptionList88.ListCount
sValue = ";"
Else
sValue = sValue & sChar
End If
Next iCount

For iCount1 = 1 To Len(sTemp1) + 1
sChar = Mid(sTemp1, iCount1, 1)
If StrComp(sChar, ";") = 0 Or iCount1 = Len(sTemp) + 1 Then
bFound = False
Do
If StrComp(Trim(Me!ErrorCodesandCorrections.ItemData( iListItemsCount)), Trim(sValue)) = 0 Then
Me!ErrorCodesandCorrections.Selected(iListItemsCou nt) = True
bFound = True
End If
iListItemsCount1 = iListItemsCount1 + 1
Loop Until bFound = True Or iListItemsCount1 = Me!ErrorCodesandCorrections.ListCount
sValue = ";"
Else
sValue = sValue & sChar
End If
Next iCount1
End Sub
Private Sub ClearListBox()
Dim iCount As Integer
Dim iCount1 As Integer

For iCount = 0 To Me!ErrorCodeDescriptionList88.ListCount
Me!ErrorCodeDescriptionList88.Selected(iCount) = False
Next iCount

For iCount1 = 0 To Me!ErrorCodesandCorrectionsList.ListCount
Me!ErrorCodesandCorrectionsList.Selected(iCount1) = False
Next iCount1
End Sub
Private Sub Save_Record_Click()
Dim oItem As Variant
Dim oItem1 As Variant
Dim sTemp As String
Dim sTemp1 As String
Dim iCount As Integer
Dim iCount1 As Integer

iCount = 0
If Me!ErrorCodeDescriptionList88.ItemsSelected.Count 0 Then
For Each oItem In Me!ErrorCodeDescriptionList88.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!ErrorCodeDescriptionList88.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & ";" & Me!ErrorCodeDescriptionList88.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

iCount1 = 0
If Me!ErrorCodesandCorrectionsList.ItemsSelected.Coun t 0 Then
For Each oItem1 In Me!ErrorCodesandCorrectionsList.ItemsSelected
If iCount1 = 0 Then
sTemp1 = sTemp & Me!ErrorCodesandCorrectionsList.ItemData(oItem)
iCount1 = iCount1 + 1
Else
sTemp1 = sTemp1 & ";" & Me!ErrorCodesandCorrectionsList.ItemData(oItem)
iCount1 = iCount1 + 1
End If
Next oItem1
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!ErrorCodeDescription.Value = sTemp
Me!ErrorCodesandCorrectionsList.Value = sTemp1


End Sub
Private Sub clrList_Click()
Call ClearListBox
Me!ErrorCodeDescription.Value = Null
Me!ErrorCodesandCorrections.Value = Null
End Sub Attached Files TrackingSystem3.zip (151.3 KB, 0 views) Reply With Quote 08-03-2012, 12:44 PM #2 ssanfu VIP Windows XP Access 2000 Join Date Sep 2010 Location Anchorage, Alaska, USA Posts 1,932 I don't have A2K7, so I couldn't open your dB. I have no idea what you are trying to do, so I made a form and a couple of list boxes to test your code. I put the code behind a button so I could run it when I wanted. This is what I found so far.

What are your list box names????
--------------------------------
In the sub Form_Current(), you have two list boxes named "ErrorCodeDescriptionList88" and "ErrorCodesandCorrections"

In the subs Sub ClearListBox(), you have two list boxes named "ErrorCodeDescriptionList88" and "ErrorCodesandCorrectionsList"

In the sub Sub Save_Record_Click(), you have two list boxes named "ErrorCodeDescriptionList88" and "ErrorCodesandCorrectionsList"

In the sub Sub clrList_Click(), you have two list boxes named "ErrorCodeDescription" and "ErrorCodesandCorrections"


In the sub Form_Current(), it looks like the list boxes are multi-select (because this of Me!ErrorCodeDescriptionList88.ItemData(iListItemsC ount))
Walking through the code, these two variables have a semi-colon:
Code: sTemp = nz(Me!ErrorCodeDescriptionList88.Value, ";") sTemp1 = nz(Me!ErrorCodesandCorrections.Value, ";") If the list boxes are multi-select, you cannot get the values this way.


Next there is a line:
Code: Call ClearListBox The list boxes are cleared of any selections before any other code uses the list boxes... ??


Next:
Code: If StrComp(Trim(Me!ErrorCodeDescriptionList88.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then The variable "sValue" is not initalized, so it is NULL. Comparing a NULL with anything (including NULL) results in a NULL. So "NULL = 0" is FALSE.
And there are no items selected because of the call to "ClearListBox".


Then the Do - Loop....The first time through the the DO loop is OK.
The second time it goes into an endless look because "bFound" is never TRUE and "iListItemsCount" doesn't get reset - it keeps increasing. It starts off greater than Me!ErrorCodeDescriptionList88.ListCount.
Code: Loop Until bFound = True Or iListItemsCount = Me!ErrorCodeDescriptionList88.ListCount (this might be the cause of the error message)

That is as far as I got with the code.
--------------------------------------------------

These two lines should be at the top of every code page:
Code: Option Compare Database Option Explicit -----
should the code be under Sub Form1 I wouldn't think so. But I don't know what the purpose of the code is for.

-----
If you use (in A2K3) the dot (Me.) notation instead of the bang (Me!), the compiler catches undeclared variables (if Option Explicit is at the top of the page).




Hello,

I am trying to run reports in Access 2007 Database (output is an Excel file) one after the other.
The first report works perfectly, but when I try to run the second report, I get the error
"Run Time Error 1004: Method Range of Object _Global Failed"

(Both of these reports are same except the first report is for 1Month Sales and Second Report is for 6 Months).

I have to close and re-open the database every time I need to run these reports one after the other.

Here is the code:

'Rearrange Columns
.Range("E:F").Select
.Selection.Cut
.Range("M:N").Select
.ActiveSheet.Paste
.Range("G:N").Select
.Selection.Cut Destination:=Range("E:L") (This is where code fails when the second report is run with the above error message)

I have googled the error but couldn't find a satisfactory answer...




Help please! I'm using Access 2007 in Windows XP and in need of some advice.

I have a few access VBA programs written in Access 2007 to help me manipulate data that I have in a database. I've used these programs without issue for several months without a problem.

Today I installed Visio 2010 and all of a sudden none of my Access programs will run. As soon as one of my VBA macros tries to execute a sql query the program fails and I get an error message that says:

Run-time error '-214748113 (8000ffff)':
Method 'Connection' of object '_CurrentProject' failed

I get this error message in other databases as well anytime I'm trying to execute a sql query via code.

An example of a type of query I'm running via code:

CurrentProject.Connection.Execute "Update RawData set Subject_Name = 'English' where Subject_Name = 'Reading and Writing'"

This has functioned without fail for several months. I've made no changes except for installing Visio 2010 earlier today.

so the progression is:

- everything is fine, Access 2007 macros work
- install Visio 2010 and Access 2007 macros no longer work

Please help!!!!!! Thanks in advance for any help.




Method 'CurrentDb' of object '_Application' failed

I am getting the error above when I try to use the form wizard. So Win 2000 Server, Access 97 updates 1 and 2.

Thanks,

Luis.




hey
i am having a few problems

when i got to try and use the create a table using wizard i get the error message : Method 'CurrentDb' of object '_Application' failed

i went online and it told me that a specific VBA reference needs to be added. (Dao360.dll)

i found it on my system via browse in under tools/references. ("C:Program FilesCommon FilesMicrosoft SharedDAODao360.dll")

but when i click ok, it comes up with the error : Error in loading DLL

i have tried everything from registering the file again via regsvr32, to reinstallin the whole office suite.

i am using office 2003

any help will be greatly appreciated

thanks




I have a form (EventComms1) with a subform (Communcation subform)that when I select an item from the combobox (cmboCommunicationName) should update a date field (CommunicationDueDate) on my subform based on the date on the main form (EventTimeStartDay) but it says the "Method 'item' of object 'forms' failed. Here is the code (which isn't elegant but worked when I applied it in another place so apart from the forms and subform syntax works). I know its something to do with my syntax but I have looked in help and copied what I thought should work:

	Code:
	 
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 1 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -7,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 2 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -4,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 3 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -1,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 4 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -1,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 5 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 7,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 6 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 7,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 7 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 14,
[Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 8 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 14,
[Forms]![EventComms1]![EventTimeStartDay])
End If

Can anyone see what i'm doing wrong please. Thanks




I have this add data form. When I click on the add button it runs a series of DoCmd.RunSQL commands to make the additions.

It's developed in Access 2002 as a 2000 file. It runs fine on Access 2002, but on SOME Windows 2000 running Access 2000 machines, I encouter this error. I ran the debugger to find out where the error is occuring:

Me!txtSampleID = LocIDs(N)

LocIDs(100) is declared as Integer, and this line above is set in a loop.

The error I get is a Microsoft Access error: Method 'Value' of object '_Textbox' failed

I tried changing the line to

Me.txtSampleID.Value = LocIDs(N)

Now it crashes the entire program.

I've encoutered this error before in another subprocedure. I got around it by putting in some error trapping code, forcing it to run, and this worked. But doesn't work here.




I'm getting - Method 'InsideHeight' of object 'PlotArea' failed. I'm using Access Automation to build Powerpoint presentations and on a slide where I have 2 charts I'm lining up the 2 chart's plot areas so the data on each chart line up. The error does not happen every time I run the code. It appears to happen randomly. I tried putting a Doevents in the code in case there was a timing issue but that has not corrected it. Now once I get the error and the code breaks I do a next and sometimes it continues and other time I get the error again. If I continue to do Next eventually it continues (could be 3 or 4 times before it runs). Here is my code:

Public Sub Lineup_Charts(xChart1 As Powerpoint.Chart, xChart2 As Powerpoint.Chart)
On Error GoTo Lineup_Charts_Err

Dim sChart1Top As Single
Dim sChart2Top As Single
Dim sChart1Left As Single
Dim sChart2Left As Single

' xChart1 is the data Chart
' xChart2 is the norm Chart
'
DoEvents
xChart2.PlotArea.InsideHeight = xChart1.PlotArea.InsideHeight ' This is where it fails

xChart2.PlotArea.InsideWidth = xChart1.PlotArea.InsideWidth
'
sChart1Top = xChart1.Parent.TOP + xChart1.PlotArea.InsideTop + xChart1.ChartArea.TOP
sChart2Top = xChart2.Parent.TOP + xChart2.PlotArea.InsideTop + xChart2.ChartArea.TOP
If sChart1Top > sChart2Top Then
xChart2.Parent.TOP = xChart2.Parent.TOP + (sChart1Top - sChart2Top)
Else
If sChart1Top < sChart2Top Then
xChart2.Parent.TOP = xChart2.Parent.TOP - (sChart2Top - sChart1Top)
End If
End If
'
sChart1Left = xChart1.Parent.Left + xChart1.PlotArea.InsideLeft + xChart1.ChartArea.Left
sChart2Left = xChart2.Parent.Left + xChart2.PlotArea.InsideLeft + xChart1.ChartArea.Left
'
If sChart1Left > sChart2Left Then
xChart2.Parent.Left = xChart2.Parent.Left + (sChart1Left - sChart2Left)
Else
If sChart1Left < sChart2Left Then
xChart2.Parent.Left = xChart2.Parent.Left - (sChart2Left - sChart1Left)
End If
End If
'
Lineup_Charts_Exit:
Exit Sub

Lineup_Charts_Err:
gErrorProcedure = "Lineup_Charts"
DoCmd.OpenForm "GenericErrorForm"
Resume Lineup_Charts_Exit

End Sub

I saw a Post on another site where the author solved the problem in Excel. In that post the answer was turning on ScreenUpdating in Excel. Now as far as I know there isn't a setting for this in Powerpoint. Does anyone have any suggestions on how I could work around this issue? Thanks for the Help!




Weird... I'm totally baffled.

If I put in an ORDER BY clause in my SQL statement, I get an error:

"Method 'Open' of object ' Recordset failed"

If I don't put the ORDER BY clause, it works fine.

Here's what I'm using:


	Code:
	Set rst=New ADODB.recordset
strSQL="SELECT temp.* FROM temp ORDER BY temp.size;"

With rst
.activeConnection=currentProject.Connection
.CursorType=adOpenKeyset
.LockType=adLockOptimistic
.Open strSQL
End With

Same thing with [temp].[size]....

Help??




Here's a part of the function I'm doing : (I'll explain prob after)

	Code:
	Private Sub Load_Association_Labels()
Dim objXML As MSXML2.DOMDocument40
Dim objStream As ADODB.Stream
Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String

Set objConn = CurrentProject.Connection
Set objRS = New ADODB.Recordset

strSQL = "SELECT Owner, Code, Value From CD_Labels L, Constants C Where L.CD_Lang__ID = C.Language Order By Owner"

Set objRS.ActiveConnection = objConn
objRS.Open strSQL, , adOpenForwardOnly, adLockReadOnly
objRS.Save objStream, adPersistXML
objRS.Close
Set objRS = Nothing
Set objConn = Nothing

Set objXML = New MSXML2.DOMDocument40
Call objXML.loadXML(objStream.ReadText())
Set objStream = Nothing


End Sub

I'm pretty confident that the chunk of code works (even if the function is currently imcomplete)

What bugs me is that I cannot understand why would this line fail :


	Code:
	Set objRS.ActiveConnection = objConn

I get this :

Quote: Run-time error '-2147457259 (80004005)'

Method 'Open' of object '_Recordset' failed I saw this error once, when I was working on a project who used COM a lot. But in the case of Access.... I'm still pretty clueless as to what might be causing it.

If it makes any difference, I'm using ADO 2.7.


LOL, forgot the most important part, I'm using MS Access 2002


Thank you for any insight you may provide.




using the below:

SQL = "SELECT * FROM Criteria_Scheme CS WHERE CS.Value0 And CS.Position0"

.ActiveConnection = p_CurrDBConn
.CursorType = adOpenKeyset
.Open SQL, p_CurrDBConn

getting Method 'Open' of object '_recordset' failed.

However if I paste the sql into a query and run it returns a record fine.

Any ideas? Have I supplied enough info?

Cheers,
Matt.




Hi all.

I've run into an ADO issue, here's what's happening:


I'm using an Excel file on a shared drive to automate an Access database in
the background.

At some point in the code I'm using ADO to export an Access dataset.

The problem occurs when the code attempts to open an ADO connection.

Please note that this is most likely a compatibility/MDAC issue; I get no errors
when I run the code from my PC.

** The error occured when another user tried to execute the code on his PC. **


Here's the VBA in question:

Dim strCon As String
Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Set con = New ADODB.Connection

strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & strAppAccFullName & ";"

con.Open ConnectionString:=strCon

The strAppAccFullName string variable is used for the full path name to the
Access database on the shared drive; its value is:

"Seukefnpcrw001DB_ZM33_review_v1.3.mdb"

The user in question has full read/write permission for the shared drive.


The error message I'm getting is:

Run-time error '-2147220999 (800401f9):

Method 'Open' of object '_Connection' failed


As for the settings etc.:

The Excel file is using the following references:

- Visual Basic for Applications
- Microsoft Excel 11.0 Object Library
- OLE Automation
- Microsoft Office 11.0 Object Library
- Microsoft Forms 2.0 Object Library
- Microsoft Access 11.0 Object Library
- Microsoft DAO 3.6 Object Library
- Microsoft ActiveX Data Objects 2.8 Library

On my PC (where the code runs fine), I'm running Office 2003 & Windows XP.
My MDAC version is 2.81.

On the PC where the error occurred, the user is running Office 2002 &
Windows XP.
The MDAC version on that PC must be 2.8xx, because I tried to install MDAC
2.8 from Microsoft and the installation failed (I was told the component is a
part of Windows).


The objective is to get the code to run without errors on the other user's
PC (Office 2002).


I should also note that the user's PC has AS400 Client Access installed.

I'm leaning toward a corrupted MDAC, as per the following resource:
http://www.dbforums.com/archive/index.php/t-321180.html


Any insights / suggestions would be greatly appreciated.

Many thanks




Greetings fellow VBA programmers,
A few days ago, several forms we have used continuously for months began producing an error: Run-time error '2683': "There is no object in this control"). All of the affected forms were simple, they only contained a progress bar control and a label. They were implemented in long-running reporting applications to keep the user apprised of the progress of the app. Through some research and trial and error, I was able to narrow the fault to the presence of the progress bar control, and found that if I removed the progress bar and added it in again the error went away.

Today, the same forms are producing a new error when they are loaded: "Run-time error '-2146500594 (800f000e)': Method 'Item' of object 'Forms' failed."

There is no code in any of the forms. The lines of code that call the form and set it equal to a variable are:


	Code:
	DoCmd.OpenForm "NDIC_Progress", acNormal
Set frmPrg = Forms("NDIC_Progress")

The error occurs on the "Set frmPrg = Forms("NDIC_Progress")" line.

These errors occur on different .mdb's and across different machines, so it must be something to do with the underlying ActiveX controls (maybe Windows Update changed something?).

Whatever the cause, I need to figure out how to fix it. Is anyone else experiencing similar issues? Even if not, does anyone have any thoughts about how I might go about fixing the problem? I have tried removing the progress bar and adding it again, but that does not change the error.

John




According to my research, VB error 80004005 (-2147467259) is an "Unspecified Error" - which isn't exactly helpful The error description in the VB error pop-up is a bit better, giving "Method 'Open' of object '_Recordset' failed" (I mean "very little bit" better ).

Here's the (pared down) code fragment (the error shows up on the Open methon):


	Code:
	Private Sub Button1_Click()

    Dim recset As ADODB.Recordset
    Set recset = New ADODB.Recordset
    
    With recset
        .ActiveConnection = CurrentProject.connection
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        
        .Open Source:="SELECT * from Names" '', Options:=adCmdText
        '' ^
        '' |
        '' +---- Error on this line

    End With
    
End Sub


I'm running Access 6 (11.5614.5606) with VB 6.3


ALL HELP WELCOME!




I found a function to concatenate the results of a sql query. I want to use it as an aggregate string function in a sql statement. The function is:

module: 'modConcat' code:
------------------------------------------------


	Code:
	Option Compare Database
Option Explicit

Public Function Concat(pstrSQL As String, Optional pstrDelim As String = ",") As String
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '======     comment out ADO below    =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '======     comment out DAO above     ======
    Dim rs As New ADODB.Recordset
    'MsgBox pstrSQL
    rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concat = strConcat
End Function

-------------------------------------------------------------
The sample sql statement I am trying to execute is:

SELECT DISTINCT [Name], Concat("SELECT Position FROM BoardMembers WHERE Name='"+[Name]+"' ",'') AS Positions
FROM BoardMembers;

When I use that sql in a query in access, I get the error:
Method 'Open' of object '_Recordset' failed

And when I tuse the sql call on my asp webpage, I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Concat' in expression.

The database works fine, and the sql is tracing correctly. My ultimate goal is to use this Concat function on from a sql statement on the webpage, but I get the undefined function error. Do you know how to make the function call work?




I have just installed Office 2003 and I am trying to use an old database in Access 2000 file format.

Within the forms are activeX Calender controls. I am now getting a runtime error when I try reference these controls:

Runtime Error - Method 'Value' of object '_CustomControl' failed.

This happens when I am trying to either change the control or get a value from it.

i.e. Me.CalControl = Now() or Me.FrmDate = Me.CalControl

I know that the controls are referenced (Microsoft Access Calender Control 7.0)

Can anyone help please?