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

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" wrote in message
...
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

Are you asking how to break Your text into multiple rows? Or are you asking how to get multiple lines of data into an Access field. Also, why don't you use the Memo data type in Access.

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/...badacexportado
Sub ADOFromExcelToAccess()
' exports data from the active worksheet 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 = ActiveSheet.Range("N65536").End(xlUp).Row '
' all records in a table
r = 1 ' the start row in the worksheet
'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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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/...badacexportado
Sub ADOFromExcelToAccess()
' exports data from the active worksheet 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 = ActiveSheet.Range("N65536").End(xlUp).Row '
' all records in a table
r = 1 ' the start row in the worksheet
'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" wrote in message
...
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" wrote in message
...
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

Many thanks to you both. Will try memo.

Regards.

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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/...badacexportado
Sub ADOFromExcelToAccess()
' exports data from the active worksheet 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 = ActiveSheet.Range("N65536").End(xlUp).Row '
' all records in a table
r = 1 ' the start row in the worksheet
'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" wrote in message
...
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" wrote in message
...
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

"Stuart" wrote in message ...

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.
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...

A better approach is to use the database as a dumb data store.
Continue to display the data in Excel (e.g. on a useform or a
worksheet) and use ADO to access the data.

I can see no reason for using MS Access to display the data (and not
just because I'm biased against MS Access forms). You are already
experiencing problems with MS Access not being like Excel when it
comes to displaying information. You admit to only using the database
for multi-user access to the underlying data. And you are comfortable
with ADO in Excel VBA code. So why use MS Access for display purposes?

--

I'm not at all sure I understand what you are suggesting, but
I'd be interested to know more.

Regards.

"onedaywhen" wrote in message
om...
"Stuart" wrote in message
...

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.
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...

A better approach is to use the database as a dumb data store.
Continue to display the data in Excel (e.g. on a useform or a
worksheet) and use ADO to access the data.

I can see no reason for using MS Access to display the data (and not
just because I'm biased against MS Access forms). You are already
experiencing problems with MS Access not being like Excel when it
comes to displaying information. You admit to only using the database
for multi-user access to the underlying data. And you are comfortable
with ADO in Excel VBA code. So why use MS Access for display purposes?

--


---
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

"Stuart" wrote in message ...
I'm not at all sure I understand what you are suggesting, but
I'd be interested to know more.


I'll try and explain how I see things.

In the beginning was Jet. It is a file-based database (ISAM). All the
usual database elements (schema definitions - tables, columns,
constraints, etc - stored procedures and data) are contained in one
file with a .mdb extension. It can support up to around eight
concurrent users, many more if you get your 'disconnected' data access
strategy right. Add a .mdw file and you have enhanced security with
user accounts. To create, maintain and use a Jet database you require
Jet install itself plus MDAC installed. Happily, both ship with most
versions of MS Office so if you are an Office developer you are pretty
well assured your users will have them; otherwise they can be
downloaded from MSDN.

Then there is MS Access, an application which ships with the more
expensive versions of Office and has two main functions: 1) some GUI
tools for creating and maintaining Jet databases and 2) an RAD
application development environment: forms and reports, essentially.
Both sit on top of Jet, are a GUI front end for Jet - for example, MS
Access forms and reports are stored in Jet tables. (A MS Access
application can also be a front end for e.g. SQL Server, rather than
Jet, but let's not complicate matters.)

The general perception is that a Jet database is a MS Access database,
a perception that is perpetuated in many articles on MSDN (I guess
this does nothing to harm sales of MS Access). However, you do not
need the MS Access GUI to create, maintain and use a Jet database. You
can do all this using the ADO libraries, which ship with MDAC, and the
OLE DB provider for Jet, which ships with Jet.

Of course, it helps if you know what you are doing. It is generally
preferable to use GUI tools for the creating and maintaining bit
(although admit to preferring to use a DDL SQL script: once written,
takes seconds to DROP all the elements and re-CREATE them). I designed
tables using MS Access long before I started doing the same with ADO
alone.

However, whether to use MS Access's application development
environment is a lifestyle choice. Remember, even if you use its GUI
tools to maintain you back end database you don't have to its forms
and reports for the front end. Horses for courses and it's my opinion
that MS Access is fine for the 'back end' tools (although can't do
everything: yesterday I was compelled to use SQL to create a CHECK
constraint where I would've happily used MS Access if possible) but
lousy for the front end environment. MS Access hides a lot from the
developer and generally has its own way of doing things. I'm always
warning people that MS Access discourages 'transferable' developer
skills and can take many months to 'unlearn' when the time comes to
move to a more capable DBMS.

Now, this is how is see your situation. You application is in Excel -
it sounds like was in Excel before 'MS Access' came into the picture.
You're data is now stored in tables in a Jet database. You are already
using ADO to read the data into the database. So why not use ADO to
read the data back into your Excel app when you need to display it? Mr
Erlandsen has some read-rolled procedures for reading the data back to
a worksheet. If you retain the data in memory in the form of an ADO
recordset or array you can display it however you like e.g. in a
control on an Excel userform. A MS Access form just doesn't fit into
the picture how I see it.

--