Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 28th, 2004, 09:09 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Method 'Open' of object '_Recordset' failed

I've worked on this for many hours and found basically nothing, i'm starting to think that this maybe a huge flaw in the

program language itself since there maybe no fix for it.

Using:
- compiler->Visual Basic 6.0
- o/s->Windows XP SP2
- reference->Microsoft ActiveX Data Objects

Checked thoroughly:
- I'm using service pack 2 windows xp which means its fully updated.
- every instance of recordset when opened is closed.
- The query works perfectly.
- In database.mdb the position field is a number and has a value.
- both sql and database strings passed into function are always valid.

Problem?:
- It just won't open inside mysql_query function


Info:
Every recordset i've used in this program seems to work
perfect except when i use the '=' sign or 'LIKE'
when compairing values in the sql string. I've checked
the sql string to see if it works and it does perfectly,
but it gives this error when used in the function.
Out of curiousity i removed the 'WHERE position=1' part
and it opened successfully.. to state this more blunt
'WHERE position=1' seems to be the problem.


SQL = "SELECT * FROM tblArmors WHERE position=1"
DataBase = "./database.mdb"

Function MySQL_Query(SQL As String, DataBase As String) As ADODB.recordset

    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.recordset

    conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.Path & "\" & DataBase & ";Mode=Read|Write"
    conConnection.CursorLocation = adUseClient
    conConnection.Open

    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = SQL
        .CommandType = adCmdText
    End With

    With rstRecordSet
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Open cmdCommand
    End With

    Set MySQL_Query = rstRecordSet

End Function


Final Note:
If any of you have any ideas on what i can try next, or if you somehow have a solution, pls post.
Reply With Quote
  #2 (permalink)  
Old September 28th, 2004, 09:15 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Forgot to say this, When the program crashed with the error in debug mode it pointed to the following line in mysql_query().

".Open cmdCommand"

Reply With Quote
  #3 (permalink)  
Old October 5th, 2004, 07:16 AM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to thchatha
Default

just replace funtion decalriotnto this
Function MySQL_Query(SQL As String, DataBase As String) As New ADODB.recordset
 it will work


Reply With Quote
  #4 (permalink)  
Old October 18th, 2004, 04:11 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am having the same problem, and when I try to add the as new into the function call I get an error: Expected: type name

If i remove the new it runs and I just hit the problem of no where statement in the sql.

Any luck?

Reply With Quote
  #5 (permalink)  
Old October 28th, 2004, 08:34 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am having the same exact problem too and cannot find a solution. Mine is with an Access database though.

If anyone has resolved this, please post.
Thanks!

Reply With Quote
  #6 (permalink)  
Old October 28th, 2004, 10:57 PM
Authorized User
 
Join Date: Oct 2004
Location: Austin, TX, USA.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First, when you use the LIKE predicate in ADO, you must use ANSI wildcards - % instead of *. And you don't need the command object. A simple

  rstRecordset.Open SQL, conConnection, adOpenStatic, adLockOptimistic

.. should do it.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
Reply With Quote
  #7 (permalink)  
Old November 8th, 2004, 11:38 PM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i think the problem is the fields , maybe the same as the reserved word.
try changing the fields or placeing a bracket like: [section] when querying...

Reply With Quote
  #8 (permalink)  
Old December 14th, 2006, 12:30 AM
Registered User
 
Join Date: Dec 2006
Location: Hamilton, South Lanarkshire, United Kingdom.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I had the same problem, turned out to be a clash with a vb6 keyword, I had a database field called 'position' which I renamed and query worked fine.

Hello
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Method 'Range' of _Global' Failed accessing Excel Socko Beginning VB 6 0 January 29th, 2008 08:33 AM
login Failed on my report using PUSH Method melvik Crystal Reports 1 November 9th, 2006 07:35 AM
Failed to Open rowset! brettdalldorf Crystal Reports 1 August 7th, 2006 12:56 PM
"Failed to open a rowset" veera_c Crystal Reports 0 May 16th, 2006 07:37 AM
METHOD 'RANGE' OF OBJECT '_GLOBAL' FAILED CBCHIAM Excel VBA 7 August 31st, 2005 04:08 AM



All times are GMT -4. The time now is 08:26 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.