ok, related tables... ACcESS in VB 6.0 *resolved*

[wpearsall]

ok, heres the deal...

i have this code [just the part that im using]

Option Explicit
 
Private Sub cmdRefresh_Click()
On Error GoTo ErrHand:
 
    Dim sSQL As String
    sSQL = "SELECT BannedMembers.BanDate, BannedMembers.BanReason, Member_Details.MemberID, " & _
           "(Member_Details.Title & ' ' & Member_Details.Surname & ', ' & Member_Details.Forename) AS FullName" & _
           "FROM Member_Details INNER JOIN BannedMembers ON Member_Details.RecordID = BannedMembers.MemberRecID;"
    
    Dim rsBan As New ADODB.Recordset
    rsBan.Open sSQL, ConnOBData, adOpenKeyset, adLockReadOnly
    
    If rsBan.RecordCount > 0 Then
        Dim x As ListItem
        Do Until rsBan.EOF
            Set x = lvBans.ListItems.Add(, , rsBan("MemberID"))
            x.SubItems(1) = rsBan("FullName")
            x.SubItems(2) = rsBan("BanReason")
            x.SubItems(3) = rsBan("BanDate")
 
            rsBan.MoveNext
        Loop
    End If
    
    rsBan.Close
    Set rsBan = Nothing
    
Exit Sub
ErrHand:
    Call LogError
End Sub

now this basically needs to show the information of the member name, when they are banned til, and why they were banned [so on the discretion of the manager, a ban can be lifted...]

problem is...

---------------------------
Membership Manager
---------------------------
Error #-2147217887 was generated by Microsoft OLE DB Provider for ODBC Drivers. ODBC driver does not support the requested properties.
---------------------------
OK
---------------------------

now im pretty certain its because of the ado code im using doesnt accept all the access stuff the same way... so someone with a bit of know how help me convert it please? [i could just do a load of vb code i know, but i want this simple as possible lol]

SELECT BannedMembers.BanDate, BannedMembers.BanReason, Member_Details.MemberID, (Member_Details.Title & ' ' & Member_Details.Surname & ', ' & Member_Details.Forename) AS FullName
FROM Member_Details INNER JOIN BannedMembers ON Member_Details.RecordID = BannedMembers.MemberRecID;

that works for me when i run it in access,,, so no idea why not in vb... ta

[si_the_geek]

Moved to Database Development forum

The problem is most likely to be your parameters to the .Open method - I suspect that ", adOpenKeyset, adLockReadOnly" is not a valid combination.

Try using ", adOpenForwardOnly, , adLockReadOnly" instead. Note that you should change line 14 to "If Not rsBan.EOF Then", as it is more efficient, and doesn't have problems with .RecordCount being -1 (which it often is - meaning "there are records, but the amount is currently unknown").

[FunkyDexter]

Looks like it should be fine to me except you would need to remove the semi colon from the end of the SQL statement.

What line do you get the error on?

[wpearsall]

the .open

it doesnt go past it, so not sure... thanks bout the .eof (used the .recordcount a few times so will change those too :P)

n e way tnx, got work now so will try it when i get bk see ya

[jp140768]

In Access, if you open a new query and chose the SQL option, and then paste in your SQL statement and try and run it, does it work?

If it does, then the problem may be with your connection, if not there should be a clearer message as to what the problem is.

HTH

[wpearsall]

In Access, if you open a new query and chose the SQL option, and then paste in your SQL statement and try and run it, does it work?

If it does, then the problem may be with your connection, if not there should be a clearer message as to what the problem is.

HTH

---------------------------
Membership Manager
---------------------------
Error #-2147217900 was generated by Microsoft OLE DB Provider for ODBC Drivers. [Microsoft][ODBC Microsoft Access Driver] The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
---------------------------
OK
---------------------------

hey guys, changed it to

rsBan.Open sSQL, ConnOBData, adOpenForwardOnly, adLockReadOnly

but no look still, got the error as above... now... ALL TABLES AND FIELDS ARE NAMED CORRECTLY (sorry for caps but just wanted to get that point across) it works in SQL view of access so could it be the ado im using? i have no end of other queries working, but they dont use any joins or that, just simple

select [fields] from [single table] WHERE [wotever]

if u no wot i mean, Microsoft Active X Data Objkects 2.0 library that im using.

do i need to alter the database to perform the task at hand? [ i didnt have to to run the query, but the actual app is using the database and dont wanna risk altering it, unless i have to cuz there are thousands of memberships and meh, boss wud be a little... p***ed wi me if u no wot i mean lol. tnx guys

[jp140768]

Again I'll suggest posting the body of the SQL statement into Access, this will show you whats wrong.

The message you have supplied, suggests that you have either used a reserved word, or used the wrong symbol eg a coma where it shouldn't be.

[wpearsall]

Again I'll suggest posting the body of the SQL statement into Access, this will show you whats wrong.

The message you have supplied, suggests that you have either used a reserved word, or used the wrong symbol eg a coma where it shouldn't be.

as seen in the images below. the EXACT SAME SQL statemement works in Access. so i'm guessing it isnt a comma etc?

I do not alter the sql in vb at all. if i create the SQL in access is there any way to run the SQL query through the ado connection?

im guessing its because of the join function in the SQL statement linking bannedmembers and member_details (i know the FullName part of the query works, because i have used it before without problems)

[snappel]

The connection object, ConnOBData. Is it definitely open and working properly? Have you looked at the connection state to ensure this, or used it elsewhere in the app? It's the only thing I can think of.

[wpearsall]

Looks like it should be fine to me except you would need to remove the semi colon from the end of the SQL statement.

What line do you get the error on?

no luck without the semi colon either. i cant remember why, but for some reason some sql statements i used didnt work without it... so...

the error is actually on the sql statement. removed my own error handling and bang on the sql...

[wpearsall]

The connection object, ConnOBData. Is it definitely open and working properly? Have you looked at the connection state to ensure this, or used it elsewhere in the app? It's the only thing I can think of.

the connection is constantly open. i use the one connection throughout the app. single database, so no point having more than one connection, i can add, delete update, and search other queries, its only this query thats the problem...

'connect to the member database
    ConnOBData.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                    "Dbq=" & App.Path & "\members.mdb;" & _
                    "Uid=admin;" & _
                    "Pwd=;"
    
    DoEvents
    
    ' list the last five members to join the club
    Call GetLastFive
    DoEvents

the getlastfive sub lists the last five membets, its run every time the app logs in. it doesnt close until the app exits, or i preform database backups etc...

but even then, if the connection was closed the error would say something to the likes of that... :S?

[si_the_geek]

Try changing your connection string - and use an OLEDB provider, rather than the (older) ODBC one; eg:

    ConnOBData.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & App.Path & "\members.mdb;" & _
                    "User Id=admin;" & _
                    "Password=;"

It may sound odd, but the provider is what actually does all the work, so it makes a big difference to what SQL statements are supported.

[wpearsall]

---------------------------
Membership Manager
---------------------------
Error #-2147217900 was generated by Microsoft JET Database Engine. The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
---------------------------
OK
---------------------------

still no luck... on the plus side, this thread as made my app work a bit faster [unless my pc is working better than it has been the past few weeks].

anyway. on this bein the second time of writing this post [after a little reset button mishap (i'm sure i int a **** since we all must of been there?)] i am just gonna get straight on...

has anyone else used the

INNER JOIN BannedMembers ON Member_Details.RecordID = BannedMembers.MemberRecID

part of the sql statement in vb before or is it something that will only work in access

i've took no end of bits out of the sql statement included the one above, but still the same error... so... hopefully from the sql statement above you can see what i want...



I wanna display
TABLE> BannedMembers
------
BanDate
BanReason

TABLE> Member_Details
------
MemberID
(FullName)> [Title] [Surname], [Forename]

any chance someone might have a working sql statement? Thanks.

[si_the_geek]

I've seen/used that kind of SQL statement many times, so it should work fine... I suspect that the issue is to do with Reserved Words, as implied by the error message.

The list of words that are reserved depends on the technology you are using (each has their own), and they can be found in this FAQ article.

To correct the issue it is best to rename the fields, however you can surround the field names in the SQL statement with square brackets instead, eg:

SELECT BannedMembers.[BanDate], BannedMembers.[BanReason], ...

[wpearsall]

hey dude! cheers. the [] fixed the problem...

(i can swear i tried that before but i guess not? lol) had to enclose all the field names with [], got to the point where i was close to

select [BannedMembers].*, [Member_Details].* from

that seemed to work. meh [tablename].[fieldname] (even ... AS [FullName])

so thanks mate, now resolved.