ACCESS FEED: RE: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box created with ALL changes

Terence-

It's not really the number of tables. You say there's a HUGE amount of data.
That would concern me more. If the data file starts getting close to 100K, then
it's time to go to SQL Server.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
Sent: Friday, August 26, 2011 10:04 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
created with ALL changes

John, going back to the fact that I have 40 tables.

OK OK, I agree its bizare, BUT, do you anticipate degredations or problems of
any sort ?

I am concerned as well, because I never imagined it getting this odd. Its not
complex, just lots of data.

This is not a ordinary Physical therapy clinic. Its a combination of Palmetto
Hospital and the University of South carolina geriatric Reasearch. Hence the
HUGE amiount of stats.
terence

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Terence-
>
> Neither. The problem with the Attachment data type is it is a Multi-Value
> field. You cannot include an Attachment field in an Append (INSERT) query.
If
> you want to see all the values in a query, you have to explode out the
> underlying data - kind of like doing a 1-many join. Attachments in and of
> themselves are relatively efficient - much more so than the old OLE Object
data
> type. If all they're doing is using the field as a repository for scanned
> documents, then that's probably OK.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> Sent: Friday, August 26, 2011 9:34 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
> created with ALL changes
>
> I am doing my best and hardest to get them away from attachment fields. User's
> LOVE them. In fact obne of the reasons I ended up with all these bloody popup
> forms with all these bloody tables, was in an effort to prevent them from
taking
> their paper copies and wanting to scan them in.
>
> In some cases, One particular, the actaully get a paper generated readout from
a
> Driving simulator, that one they must have on record, some how. My choices
are:
>
> 1. they could re-type it intp a form.
> 2. Keep the paper in a folder...trying to get away from that.
> 3. Scan it as an attachemnt.
>
> Regarding attachments, Is it database size and Blot that I should be concerned
> about, or is it speed ?
>
> terence
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Terence-
> >
> > What's in the Attachment field? Attachment and Multi-Value fields are bad,
> bad
> > things. You could avoid it by doing:
> >
> > If rst.Fields(intI).Type <> dbAttachment Then ...
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > Sent: Friday, August 26, 2011 8:56 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text box
> > created with ALL changes
> >
> > John, ty for the set db to current, it's late :)
> >
> > Code works, with the exception that it failed when it found an attachment
> field.
> >
> > I will move it to the end of the table, and cut short the iterations by 1.
> > terence
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Terence-
> > >
> > > You forgot to Set db = CurrentDb.
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > Sent: Friday, August 26, 2011 8:31 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text
box
> > > created with ALL changes
> > >
> > > John,
> > >
> > > I just tried the code as follows, as you sent
> > >
> > > Private Sub Command1_Click()
> > > Dim db As Database
> > > Dim strResult As String, rst As DAO.Recordset, intI As Integer
> > > strtbl = "INFO"
> > > Set rst = db.OpenRecordset(strtbl, dbOpenDynaset)
> > > If Not rst.EOF Then
> > > For intI = 0 To rst.Fields.Count - 1
> > > strResult = strResult & rst(intI) & ", "
> > > Next intI
> > > End If
> > > End Sub
> > >
> > > I get a error 91 Object variable or with block variable not set
> > > I dont see the prob ?
> > >
> > > Terence
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
> wrote:
> > > >
> > > > Terence-
> > > >
> > > > Yes, but for how many records? Is there only one record in each of the
40
> > > > tables? To get all the fields concatenated from a record into one
string,
> > do:
> > > >
> > > > Dim strResult As String, rst As DAO.Recordset, intI as Integer
> > > >
> > > > Set rst = db.OpenRecordset(strTbl, dbOpenDynaset)
> > > > If Not rst.EOF Then
> > > > For intI = 0 To rst.Fields.Count - 1
> > > > strResult = strResult & rst(intI) & ", "
> > > > Next intI
> > > > End If
> > > >
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > http://www.viescas.com/
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > > -----Original Message-----
> > > > From: MS_Access_Professionals@yahoogroups.com
> > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Terence
> > > > Sent: Friday, August 26, 2011 7:46 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> > > > Subject: [MS_AccessPros] Re: Suggestion/Ideas plz, Customer wants a text
> box
> > > > created with ALL changes
> > > >
> > > > ok, how about this,
> > > >
> > > > I can get the count of how many Fields are in each table as below
> > > >
> > > > Dim db As Database
> > > > Dim strtbl As String
> > > > strtbl = "INFO"
> > > > Set db = CurrentDb
> > > > MsgBox db.TableDefs(strtbl).Fields.Count
> > > >
> > > > is there a way I can retrieve the content of each individual field like
> > > walking
> > > > a subscripted array ?
> > > >
> > > > Terence
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, "Terence"
> <buffalome90210@>
> > > > wrote:
> > > > >
> > > > >
> > > > >
> > > > > I have have about 40 tables in this database, which may or may be
> created
> > d=
> > > > > uring an "Encounter". My customer wants all the data that is added
into
> > tho=
> > > > > se tables during that "Encounter" dumped into a text box, we are not
> > concer=
> > > > > ned with format. We dont even care if there are associated label or
just
> > th=
> > > > > e data, I just need the stuff shoved in a box.
> > > > >
> > > > >
> > > > > There are aprox 400 individual elements, so I dont want to write code
to
> > do=
> > > > > them all. Any one have a clever idea?
> > > > > terence
> > > > >
> > > > > I am thinking something with a Star (*)...like a me.dumpbox =3D
> > me.recordse=
> > > > > t
> > > > >
> > > > > or making some sort of a continuos for, or a datasheet, I just dont
know
> ?
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links