[AccessD] Need more than 255 fields! (was - Hit the Wall?)

Thank you Drew!

This is a whole area I don't know about, and your example looks like a great
example to learn from.

Dan Waters

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Friday, July 23, 2004 3:49 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Need more than 255 fields! (was - Hit the Wall?)

As an addition to the email below, if you go this method, you can really
save on form creation time, if you build your form one table at a time.  Set
the datasource of the form to the table.  Put the fields on the table.  then
run code like this:

Dim ctrl As Control
Dim strCode As String
Dim f As Long
For Each ctrl In Me.Controls
    If TypeOf ctrl Is TextBox Or typeofctrl Is ComboBox Or typeofctrl Is
CheckBox Then
        ctrl.Name = Me.RecordSource & "." & ctrl.ControlSource
        ctrl.ControlSource = ""
        ctrl.AfterUpdate = "[Event Procedure]"
        strCode = strCode & vbCrLf & "Private Sub " & Replace(ctrl.Name,
".", "_") & "_AfterUpdate()" & _
        vbCrLf & "dim dp As DataPoint" & vbCrLf & "Set
dp=dps.DataPointInfoByName(""" & _
        ctrl.Name & """)" & vbCrLf & "dp.Value=Me(" & ctrl.Name & ")" &
vbCrLf & _
        "Set dp=Nothing" & vbCrLf & "End Sub" & vbCrLf
    End If
Next
f = FreeFile
Open "C:\" & Me.RecordSource & "Code.txt" For Binary Access Write As f
Put f, , strCode
Close f

Which would change the control names to Table.FieldName and build a little
text file that you can just copy and paste into the code module behind the
form, to handle the AfterUpdate events for those controls.  Run that code
every time you set a new table as the recordsource for the form, and it
should drastically reduce development time.  You may need to tweak that
code, if you plan on putting in other events, etc.  Also, the code is only
looking for textboxes, comboboxes and checkboxes, you'd need to add any
other controls that you may want to have 'unbound' data connected to them.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of
DWUTKA at marlow.com
Sent: Friday, July 23, 2004 3:28 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Need more than 255 fields! (was - Hit the Wall?)


Yeah, build a class like this:

Public Field1A
Public Field2A
Public Field1B
Public Field2B
Dim intID as Long
Property Get ID() As Long
ID=intID
End Property
Property Let ID(intEnter as Long)
intID=intEnter
dim rs as adodb.recordset
dim strSQL as string
strSQL="SELECT * FROM tblA WHERE ID=" & intID
Set rs=new adodb.recordset
rs.open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If rs.EOF=True and rs.BOF=True then 'This catches anything where the key
doesn't exist.
	rs.close
	set rs=nothing
	exit property
end if
Field1A=rs.fields(1).value
Field2A=rs.fields(2).value
rs.close
set rs=nothing
strSQL="SELECT * FROM tblB WHERE ID=" & intID
Set rs=new adodb.recordset
rs.open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Field1B=rs.fields(1).value
Field2B=rs.fields(2).value
rs.close
set rs=nothing
End Property

The above would work for an example, but quite frankly, I think a better
approach, if you are looking at an ever expanding set of fields, instead of
creating a property to hold the specific value, create a property that
retrieves it's value from it's name.

In other words, build a Class Like This:

Public FieldName As String
Public TableName as String
Public Value as Variant

Then, build a table, that lists the tables 'involved', along with their ID
field.  Have your 'Main' Class look like this: (Assume the above class is
DataPoint)

Dim DPs As Collection
dim intID as Long
Property Get DataPointCount() As Long
DataPointCount=DPs.Count
End Property
Property Get DataPointInfoByName(strEnter as string) As DataPoint
Set DataPointInfoByName=DPs(strEnter)
End Property
Property Get ID() As Long
ID=intID
End Property
Property Let ID(intEnter as Long)
intID=intEnter
Dim rsTBLS As ADODB.Recordset
Dim rsData As ADODB.Recordset
Dim strSQL as String
dim dp as DataPoint
dim i as Long
strSQL="SELECT TableName, IDField FROM tblMultiTables"
set rsTBLS=new ADODB.Recordset
rsTBLS.open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
Set DPs=New Collection
If rsTBLS.EOF=False then
	rsTBLS.MoveFirst
	Do Until rsTBLS.EOF=True
		set rsDATA=New ADODB.Recordset
		strSQL="SELECT * FROM " & rsTBLS.Fields("TableName").Value &
_
		" WHERE " & rsTBLS.Fields("IDField").Value & "=" & intID
		rsDATA.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
		If rsDATA.EOF=False then 
			rsDATA.MoveFirst
			For i=0 to rsDATA.Fields.Count-1
				set dp=new DataPoint
				dp.TableName=rsTBLS("TableName").Value
				dp.FieldName=rsDATA.Fields(i).Name
				dp.Value=rsDATA.Fields(i).Value
				DPs.Add dp,dp.TableName & "." & dp.FieldName
				set dp=nothing
			Next i
		End if
		rsData.Close
		set rsDATA=Nothing
		rsTBLS.MoveNext
	Loop
End if
rsTBLS.Close
set rsTBLS=Nothing
End Sub

Now, that would retrieve all of the data, and to retrieve a value, you would
save the above Class As DataPoints, and use the Following code:

dim dps as DataPoints
dim dp as DataPoint
set dps=new DataPoints
dps.ID=1
set dp=dps.DataPointInfoByName("TableA.Field1")


Now you have dp, that has a value property representing that field (along
with that fields name, and table.  Changing dp's value property, is going to
change what's in the collection. So if you allow for changing of the data,
you can then reverse the process, for a 'Save' function in the DataPoints
class module, so that it saves the new values to the tables. 

Actually, before digging into this too far, if you do take this approach,
try to map out what your real objectives are.  There are things you can do
that would take a little more time at the start, but save tons of time in
the future.  For example, if you really want the data to save on the fly, in
the DataPoint class, put in a boolean, that on the initialization, the value
is False, then, instead of a public statement to set the property directly
to a variable, build get and let statements, and on the let statement, look
to see if the boolean is False.  If it is, the value is being set for the
first time, so do nothing. (It's being set by the DataPoints class).  If it
isn't, then the value is being changed by the user, so write something like
this:

dim strSQL As String
strSQL="UPDATE " & TableName & " SET [" & FieldName & "]=" & Value & " WHERE
" & IDField & "=" & KeyID
CurrentProject.Connection.Execute strSQL

And bing, you just updated that value in the table. Now, in the code above,
you would need to add the IDField and KeyID properties to the DataPoint
Class (and set them when you're filling the collection in the DataPoints
class).  But technically speaking, I don't really think you're users would
be able to actually perform data entry faster then what it would take for
Access to update each field on the fly like that. Of course, you loose the
'restore' capability, or 'undo', but that is also something you could build
into the DataPoint class (setting an internal oldvalue), that could be
restored with a user event.

That's what I mean about planning it out.  Classes are extremely easy to
use, the biggest obstacle is to do a little planning ahead, so as you are
building the components, the right capabilities are built into the right
components.

Just something to munch on.

Drew

P.S.--I wrote every above in Outlook, so I didn't have a VBE correcting any
types, or using intellisense, so the code above may have mistypes in it.




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Dan Waters
Sent: Friday, July 23, 2004 2:04 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Need more than 255 fields! (was - Hit the Wall?)


Now that is fascinating!

When you say multiple recordsets, do you mean one from Table A, one from
Table B, and so on?

Thanks!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Friday, July 23, 2004 1:39 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Need more than 255 fields! (was - Hit the Wall?)

Go with an unbound form.  There is no limit to how many properties a Class
can have, so build a class to represent a 'record', and fill it with
multiple recordsets.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Dan Waters
Sent: Friday, July 23, 2004 1:23 PM
To: Database Advisors
Subject: FW: [AccessD] Need more than 255 fields! (was - Hit the Wall?)


Any other ideas on ways to resolve this issue?

Thanks!
Dan Waters

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Thursday, July 22, 2004 8:45 PM
To: Database Advisors
Subject: [AccessD] Hit the Wall?

In an Access app w/FE and BE, I need a table that has about 270 fields.  A
single form will be bound to the table.  (This is a business process
management application.)  If I upsize this to a project (never done this), I
can have up to 1024 fields in a table.

 

I tried creating a query to join two smaller tables, but queries are also
limited to 255 columns.

 

Is there a way around this so I can still use an Access BE?

 

Thanks!

Dan Waters

--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com