Re: Convert from WrapText

From: Stuart (sg_booth_at_hotmail.com)
Date: 04/18/04 

Date: Sun, 18 Apr 2004 17:16:32 +0100

Many thanks to you both. Will try memo.

Regards.

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:10859nbaatjg15f@news.supernews.com...
> You don't want it in multiple rows/cells. There is no "generally" about
it.
> A record in Access or any other database is one row. (without getting
into
> relational databases or other hierarchies). If the access text field
limit
> is 255, then as Chris suggests, perhaps you need to use a memo field.
>
>
> From Access Help:
> For a Text field, type the maximum number of characters to allow in the
> field (up to 255).
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Stuart" <sg_booth@hotmail.com> wrote in message
> news:%238oCFyVJEHA.2884@TK2MSFTNGP12.phx.gbl...
> > I'm at the beginning this import process. I soon found that I received
> > an error message when importing data....basically saying that there
> > was too much data to fit in the Access field, and it was clear that
> > the colB data was the culprit. I did some research in Access Help,
> > but found little about Wraptext. What I did find, suggested that
> > Access recordsets (generally) occupy one row.
> >
> > With Google, I found an article stating that the only way to import
> > this amount of data from an Excel cell (without widening the Access
> > field to a ludicrous degree) was to use VBA, or to convert to a
> > CSV file. The article gave no details on how to achieve either way.
> >
> > The only reason I'm exploring Access is to allow users to reliably
> > work on a project at the same time. Once their work with the data
> > is complete, I would wish to take the project back into Excel, for
> > calculation, etc. I understand that using Access to share data among
> > users, is preferable than sharing workbooks under Excel..
> >
> > So ideally I wish to take the workbooks into Access and display
> > the data in as close a manner as to that displayed in Excel, then
> > export back to Excel.
> >
> > Assuming this is reasonable, could you please assist with the first
> > problem, namely the reformatting of the wraptext cell, back to
> > multiple rows?
> >
> > For reference, here is the code used for importing the Excel data:
> > http://www.erlandsendata.no/english/index.php?d=envbadacexportado
> > Sub ADOFromExcelToAccess()
> > ' exports data from the active work*** to a table in an Access
database
> > Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
> > Dim StartRw As Long, EndRw As Long
> > ' connect to the Access database
> > Set cn = New ADODB.Connection
> > cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
> > "Data Source=C:\BofQProject\Access BofQ Project\Estimate
db4.mdb;"
> > ' open a recordset
> > Set rs = New ADODB.Recordset
> > rs.Open "WorkshopDrainage", cn, adOpenKeyset, adLockOptimistic,
> > adCmdTable
> > EndRw = Active***.Range("N65536").End(xlUp).Row '
> > ' all records in a table
> > r = 1 ' the start row in the work***
> > 'take all rows, including those empty
> > Do While r < EndRw + 1
> > With rs
> > .AddNew ' create a new record
> > ' add values to each field in the record
> > .Fields("Item") = Range("A" & r).Value
> > .Fields("Description") = Range("B" & r).Value
> > .Fields("Qty") = Range("C" & r).Value
> > .Fields("Unit") = Range("D" & r).Value
> > .Fields("P Sums") = Range("E" & r).Value
> > .Fields("Labour") = Range("F" & r).Value
> > .Fields("Materials") = Range("G" & r).Value
> > .Fields("Plant") = Range("H" & r).Value
> > .Fields("Sub/Ctr") = Range("I" & r).Value
> > .Fields("Rate") = Range("J" & r).Value
> > .Fields("%") = Range("K" & r).Value
> > .Fields("%2") = Range("L" & r).Value
> > .Fields("ClientRate") = Range("M" & r).Value
> > .Fields("NettCost") = Range("N" & r).Value
> > .Fields("ClientCost") = Range("O" & r).Value
> > .Update ' stores the new record
> > End With
> > r = r + 1 ' next row
> > Loop
> > rs.Close
> > Set rs = Nothing
> > cn.Close
> > Set cn = Nothing
> > End Sub
> >
> > Regards.
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:10854ln6o2h4i96@news.supernews.com...
> > > Generally, displaying a text string on multiple rows is a formatting
> > > function.
> > >
> > > How is the user looking at the data in Access?
> > >
> > > If you can successfully display it as you want using VBA or a CSV
file,
> > then
> > > what other method are you trying to use that is unsuccessful? It is
> > unclear
> > > what you are asking?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Stuart" <sg_booth@hotmail.com> wrote in message
> > > news:uBmUQXUJEHA.3628@TK2MSFTNGP12.phx.gbl...
> > > > I have data in col B (set to width of 55) and Wraptext is enabled
> > > > in just this col. In all, each record occupies up to 15 columns.
> > > >
> > > > I wish to import this data into Access (working with Office 2000),
> > > > and have found a routine to do this. However, it seems that only
> > > > using VBA or converting the file to CSV format will allow the
> > > > Access record to occupy more than one row? I believe that to be
> > > > correct.
> > > >
> > > > In Excel, if I could 'split' the wrapped cells into a series of
rows,
> > > > and paste data accordingly, then I might end up with 3 rows
containing
> > > > the original cell value. If I then import this, then as best I can
> > guess,
> > > > Access would treat each of these rows as a recordset when imported,
> > > > even though they are really part of one Excel record.
> > > >
> > > > I need all the colB data to display in Access, since it contains the
> > > > crucial information that allows the user to modify the information
> > > > held in the related fields.
> > > >
> > > > Not sure how to proceed, so would be grateful for suggestions.
> > > >
> > > > Regards.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ---
> > > > Outgoing mail is certified Virus Free.
> > > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > > Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004
> > > >
> > > >
> > >
> > >
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004
> >
> >
>
> 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.659 / Virus Database: 423 - Release Date: 15/04/2004