Visual Basic General
SELECT Results.MemberID, Results.Position, Competitions.CompType, Competitions.CompDate, Competitions.CompID
FROM Results, Competitions
WHERE (((Results.MemberID)='lew26820') AND ((Results.CompID)=[Competitions].[CompID]));
The query itself may be okay but the important information we need to help you lies in how you are using it in your .NET piece. The error looks like a COM error so there may be an issue with how you are communicating with Access. Please post the code that surrounds your use of the query and any supporting code that relates to how you set up the connection and command to Access.
I have basically set it up like this
Dim conn as new Adodb.Connection
Dim rs_hist as new Adodb.Recordset
dim connstring as string
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\GolfCourse\gc_data.mdb;Persist Security Info=False"
dim sqlHist as string
sqlHist = Query memtioned in earlier post
conn.open(connstring)
rs_hist(sqlHist,conn)
It errors on the line
rs_hist(sqlHist,conn)
if you just pasted the sql statement exactly as is from Access it wouldn't work. Try it like this:
Dim
sqlHist As String = "SELECT Results.MemberID, Results.Position, Competitions.CompType, Competitions.CompDate, Competitions.CompID FROM(Results, Competitions) WHERE (((Results.MemberID)='" & lew26820 & "') AND ((Results.CompID)=[Competitions].[CompID]))"
hope it helps
Nope still doesnt work.
This is sending me up the wall as i already have a solution for the problem but if this worked if would speed up my solution significantly
Maybe we can eliminate the question of bad query text formaulation by choosing a simple, guaranteed correct syntax (like SELECT * FROM Results)
If that works then perhaps you can reinspect your use of the recordset. Perhaps you could use an OleDb dataadapter to pull in the data. Recordsets can be squirrely sometimes.
All my other queries work but they are relatively simple.E.g (Select * from results).
Anyway this is for c/w so it doesnt really matter if its working fast or not. ill just stick with my other solution to the problem.
Thanks anyway