QUOTE(Ricardo_UK @ 3 Jul, 2008 - 06:11 AM)
Are there many (any) of you out there using MySQL with a VB 6 front end?
Well seeing how no one else has answered this I though that I'd better post the solution that I have come up with. Still requires some tweaking but at least it's giving me roughly what I want.
After reading everything that I could get my hands on, reference ADO SHAPE UNBOUND HIERARCHICAL RECORDSETS, I have been able to come up with this...
I created an unbound empty recordset for the shape command then read my mysql tables as normal adding the results to the empty/shaped recordset.
For test purposes I added a command button and a mshflexgrid to a form.
Command7 & MSHFlexGrid2 (put the code behind the button).
I also have my connection detail to the MySql database called via SetConn
This is the normal stuff like:-
ps_conn = "PROVIDER=MSDASQL;driver={MySQL ODBC 3.51 Driver};server=127.0.0.1;uid=xxxxx;pwd=xxxxxxxxxxx;database=mydata;Option = 16427"
Hope that this make sense and helps someone else out there...
CODE
Private Sub Command7_Click()
'begin connection routine to MySQL
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
Call SetConn
db.Open ps_conn
'connection routine end
Dim i As Integer
Dim j As Integer
Dim rs As New ADODB.Recordset
Dim rsCh As ADODB.Recordset
rs.ActiveConnection = "provider=msdatashape;data provider=none;"
rs.Open " SHAPE APPEND new adInteger As PID, " & _
" New adInteger As LineNo, " & _
" New adVarChar(5) As dcustref, " & _
" New adVarChar(12) As dcallno, " & _
" New adInteger As dseqno, " & _
" New adVarChar(20) As dcolour, " & _
" New adVarChar(12) As dsize, " & _
" New adInteger As dqtyrequired, " & _
"((SHAPE APPEND new adInteger As ChID, " & _
" New adInteger As LineNo, " & _
" New adInteger As ItemNo, " & _
" New adVarChar(8) As LocationKey, " & _
" New adInteger As dqtyrequired, " & _
" New adInteger As dqtypicked) " & _
" RELATE PID TO ChID) " & _
" AS Child", , adOpenStatic, adLockOptimistic
' Need to add a record to the parent recordset
Dim adoRSsalesdespatchline As Recordset
Set adoRSsalesdespatchline = New Recordset
adoRSsalesdespatchline.CursorLocation = adUseClient
'find parent record then add to rs
adoRSsalesdespatchline.Open "SELECT despatchno, lineno, dcustref, dcallno, dseqno, dcolour, dsize, dqtyrequired FROM salesdespatchline WHERE despatchno = " & Val(Me.Text1.Text) _
, db, adOpenStatic, adLockOptimistic
If Not adoRSsalesdespatchline.EOF Then
adoRSsalesdespatchline.MoveFirst
For i = 1 To adoRSsalesdespatchline.RecordCount
rs.AddNew Array("PID", "LineNo", "dcustref", "dcallno", "dseqno", "dcolour", "dsize", "dqtyrequired"), _
Array(adoRSsalesdespatchline!lineno, adoRSsalesdespatchline!lineno, adoRSsalesdespatchline!dcustref, _
adoRSsalesdespatchline!dcallno, adoRSsalesdespatchline!dseqno, adoRSsalesdespatchline!dcolour, adoRSsalesdespatchline!dsize, _
adoRSsalesdespatchline!dqtyrequired)
'' Now add the child records related to the original parent's record
Set rsCh = rs("Child").Value
'find child record(s) then add to rs
Dim adoRSwhsalesdespatchlinedetail As Recordset
Set adoRSwhsalesdespatchlinedetail = New Recordset
adoRSwhsalesdespatchlinedetail.CursorLocation = adUseClient
adoRSwhsalesdespatchlinedetail.Open "SELECT despatchno, lineno, itemno, locationkey, dqtyrequired, dqtypicked FROM whsalesdespatchlinedetail " & _
" WHERE despatchno = " & adoRSsalesdespatchline!despatchno & " AND lineno = " & adoRSsalesdespatchline!lineno _
, db, adOpenStatic, adLockOptimistic
If Not adoRSwhsalesdespatchlinedetail.EOF Then
adoRSwhsalesdespatchlinedetail.MoveFirst
For j = 1 To adoRSwhsalesdespatchlinedetail.RecordCount
rsCh.AddNew Array("ChID", "LineNo", "ItemNo", "LocationKey", "dqtyrequired", "dqtypicked"), _
Array(adoRSwhsalesdespatchlinedetail!lineno, adoRSwhsalesdespatchlinedetail!lineno, _
adoRSwhsalesdespatchlinedetail!itemno, adoRSwhsalesdespatchlinedetail!locationkey, _
adoRSwhsalesdespatchlinedetail!dqtyrequired, adoRSwhsalesdespatchlinedetail!dqtypicked)
adoRSwhsalesdespatchlinedetail.MoveNext
Next
adoRSwhsalesdespatchlinedetail.Close
Set adoRSwhsalesdespatchlinedetail = Nothing
Else
MsgBox "EOF: whsalesdespatchlinedetail"
End If
adoRSsalesdespatchline.MoveNext
Next i
Set MSHFlexGrid2.DataSource = rs 'should just now need to set the datareport's source to this and do the layout!!!
MsgBox "Successfully Done..."
Else
MsgBox "Record NOT found!", vbExclamation
End If
adoRSsalesdespatchline.Close
rs.Close
rsCh.Close
End Sub
I'm sure that there are better ways of doing this with MySQL compatible datashaping (if it exist). I was put off using Crystal Reports - I only have an old version which isn't much good. Trying to keep the costs of development down means that I have to do things the hard/cheap way. There is also the fact that if I used Crystal I would still need to retrieve the data from different locations ie. development server / live server so would still need to code a bit just to cover that!
Regards, Ricardo
Live Fast, Die Young & Keep Your Underwear Clean!!!
2 Jul, 2008 - 02:55 AM
|