SQL, ADO and Subforms

[mgardiner]

I have been given the task of creating a form that looks "JUST LIKE" the Subform in Access. I am converting an Access database to SQL and VB. The Company wants the form to look just like the Access Form/Subform they have now.
The subform is in the form of individual controls not a table view.
I have tried this with Seperate ADODC controls and have it somewhat working.
the problem is when you try to edit or Add new. This Data is from multiple database tables. Three in fact. Even with the Three seperate ADODC controls I have the problem that there is three seperate connections to the SQL Server. Has anyone done something like this and used another method or is there a way to pull one recordset from SQL and then tie the ADODC controls to subsets of the original recordset without all the Connections to SQL.
any ideas would be helpful.
Morgan

 

[Terpsfan]

What I would do in this case is create a recordset based off of a select query that ties all the tables together, related by a common field. If you are using an ADODC control, then you can right-click the control and bring up the properties. On the Record Source tab, select adCmdText as your Command Type as adCmdTable and then select the queryname.

Or you could create an explicit MSDatashape recordset object through code relate the fields that way. You could then use the recordset.addnew and recordset.update methods to update the records to the database. You could put some of the subforms as DataGrid controls. Here is an example of code in one of my projects:

Private Sub Form_Load()
datPrimaryRS.ConnectionString = "PROVIDER = MSDataShape;Data PROVIDER = MicrosoftJet.OLEDB.3.51;Data Source = G:\QualityDatabases\QCFilesReceived_be.mdb"
datPrimaryRS.CommandType = adCmdText
datPrimaryRS.RecordSource = "SHAPE {select AcctNum,AutoID,BatchNum,Branch,CompanyNum,EntityCode,Fname,Lname,ReqPurpose from TBLACCT Order by AutoID} AS ParentCMD APPEND ({select AutoID,Location,[In],Out,TransID from TBLTRANS Order by TransID } AS ChildCMD RELATE AutoID TO AutoID) AS ChildCMD,({Select Requestor,RequestDate, ReceiveDate,AirbillIn, AirbillOut,AutoID,ReturnDate From TBLREQ } As ChildCMD1 RELATE AutoID To AutoID) As ChildCMD1"

Set grdDataGrid.DataSource = datPrimaryRS.Recordset("ChildCMD&quot.UnderlyingValue
Set DataGrid1.DataSource = datPrimaryRS.Recordset("ChildCMD1&quot.UnderlyingValue
End Sub

 

[mgardiner]

Thank You for Your Response.
I can not use any data Grids since I am to use only individual controls for each field.
but I might be able to get the idea to work with ADO controls. Is there any where
I can find out more information on shaped SQL statements?
Morgan

 

[mutidjo]

Morgan,
Did you ever resolve the issue. I'm new to vb6 and have to do the same thing from access. Thanks in advance.

 

[mgardiner]

Not to a complete satisfaction.
I wound up using 3 adodc controls.
in code when the user asks for new I ask what they want a new one of A project, A Quote, or A Quote Revision.
then I add a record with the default data that is needed to each table. if the select Project I have to add a project, a quote, and a quote revision. if the ask for a quote I have to add a quote, and a quote Revision.
If you need some help let me know.

 

[RottPaws]

You might try the DataRepeater control. It will supposedly do what you want, but I had problems getting it to work when I was trying to migrate an Access app to VB. I was then directed to forget about VB and build it as a web app, so I never worked out the bugs.

There is an article on MSDN that tells how to create and use the DataRepeater control at

http://msdn.microsoft.com/library/d...bcon98/html/vbconusingdatarepeatercontrol.asp

There is also an article on Tek-tips that seems to be a modification of the above article at

http://www.tek-tips.com/faqs.cfm?spid=222&sfid=743&CFID=64878520&CFTOKEN=82527371

Good luck. _________
Rott Paws

 

[mutidjo]

Thanks for your replies. I will try your advice, and let you know if I get it to work.

esti

 

[rymansfla]

Documentation on SHAPE command is hard to come by.

Try this

KB Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q189657

Also the data form wizard in VB6 helped me in the beginning to utilize the SHAPE commands

Good Luck