Splitting Db deletes field formatting

[Guest]

After splitting my Database, I opened the back-end to see that all (50+) fields had been reset to Text format. Additionally, all properties that I had set (default values, input mask) -- including those not available in form field properties (i.e. Required, Allow Zero Length) -- are now returned to Access defaults

I changed them all (several hours) in the back-end. When I re-opened, they all changed back to default values again. BTW, before re-opening the back-end, I imported an Excel Db, but formatted all columns to match field props. Hoping it's not the import that's causing the problem since we do this everyday (after a lot of trial and error, I don't feel that it is)

How can I prevent this from happening? And preserve field formats after the Db is split

Please help....

 

[John Nurick]

Hi Pete,

When you say all fields were "reset to Text format" do you mean that the
field type has changed (e.g. from "Autonumber" to "Text"), or that the
Format property of the field has changed? If the latter, from what to
what?

What version of Access are you using?

When you split other databases, does the same thing happen?

After splitting my Database, I opened the back-end to see that all (50+)

fields had been reset to Text format. Additionally, all properties that
I had set (default values, input mask) -- including those not available
in form field properties (i.e. Required, Allow Zero Length) -- are now
returned to Access defaults.

I changed them all (several hours) in the back-end. When I re-opened,

they all changed back to default values again. BTW, before re-opening
the back-end, I imported an Excel Db, but formatted all columns to match
field props. Hoping it's not the import that's causing the problem
since we do this everyday (after a lot of trial and error, I don't feel
that it is).

 

[Guest]

John

I now know my problem is occuring after import, and not after database split. I've tried numerous methods (10 times), and all have failed

Let me give you an overview. I've created a Db with 50 fields. In Db design mode, I've set properties for format (number, date), Required (Yes for some), Allow Zero Length (No for same), Default values. After importing an Excel file, they all return to text (except one row I emptied for experiment), and all other properties are returned to Access defaults

Before exporting, I set the format of all Excel columns to match Access formats. I sort the doc so that the most complete records are at the tope rows. No single row has all fields populated; alot have most. I let Access assign the ID key

I've tried several Excel formats, but each time it returns to default formats. What makes matters worse, the Required and Zero Length props are not available at the form level, and I'm not nearly capable of coding all of them in VB

I'm using XP, Excel 2002, Access 2002, but the Access file is 2000.

I am completely stumped. 100+ hours invested in this project, I'm hopeful you can help me figure it out. Pleeeease

----- John Nurick wrote: ----

Hi Pete

When you say all fields were "reset to Text format" do you mean that th
field type has changed (e.g. from "Autonumber" to "Text"), or that th
Format property of the field has changed? If the latter, from what t
what?

What version of Access are you using?

When you split other databases, does the same thing happen

On Wed, 19 May 2004 07:26:06 -0700, Pete Merend

After splitting my Database, I opened the back-end to see that all (50+)

fields had been reset to Text format. Additionally, all properties tha
I had set (default values, input mask) -- including those not availabl
in form field properties (i.e. Required, Allow Zero Length) -- are no
returned to Access defaultsthey all changed back to default values again. BTW, before re-openin
the back-end, I imported an Excel Db, but formatted all columns to matc
field props. Hoping it's not the import that's causing the proble
since we do this everyday (after a lot of trial and error, I don't fee
that it is)
-
John Nurick [Microsoft Access MVP

Please respond in the newgroup and not by email

 

[Doug Bell]

Pete,
When you import from XL the ISAM driver used, looks at the first few rows of
the Excel Worksheet and sets the field type to suit.

You could try importing it as raw data and then appending it to your table.

Depends on how you are importing the data. I find executing an SQL string
gives you a lot of flexibility to select desired worksheet. eg.:

stSheetname = "YourWorkSheet"
stWrkBkName ="C:\Path...\YourWrkBk.XLS"
stSQL = "INSERT INTO tblYourTable ([YourFld1], " & _
"[YourFld2]) "
stSQL = stSQL & "SELECT YourWrkBkCol1, WrkBkCol2
"FROM [" & stSheetname & "$] IN '" & stWrkBkName &
"'[EXCEL 8.0;] " & _
"WHERE ((YourWrkBkCol1) > 2);"
db.Execute stSQL

John,

I now know my problem is occuring after import, and not after database

split. I've tried numerous methods (10 times), and all have failed.

Let me give you an overview. I've created a Db with 50 fields. In Db

design mode, I've set properties for format (number, date), Required (Yes
for some), Allow Zero Length (No for same), Default values. After importing
an Excel file, they all return to text (except one row I emptied for
experiment), and all other properties are returned to Access defaults.

Before exporting, I set the format of all Excel columns to match Access

formats. I sort the doc so that the most complete records are at the tope
rows. No single row has all fields populated; alot have most. I let Access
assign the ID key.

I've tried several Excel formats, but each time it returns to default

formats. What makes matters worse, the Required and Zero Length props are
not available at the form level, and I'm not nearly capable of coding all of
them in VB.

I'm using XP, Excel 2002, Access 2002, but the Access file is 2000.

I am completely stumped. 100+ hours invested in this project, I'm hopeful

you can help me figure it out. Pleeeease.

 

[Guest]

Doug

Thanks for your suggestion. If I go the VB route, I'll definitely have to ask some more questions. Also, not sure I understand your suggestion to "import raw data, and append to table".

If you're suggesting that I import, and then re-set properties, I've tried that multiple times to no avail. When I re-open the file after import, all field properties return to defaults. I have likewise formatted every column of the Excel file, then sorted to make sure populated rows are atop. Fields re-set to defaults. But still, if it were to work, we import new records from an Excel file each and every day. Reformatting 50 fields per day will render the Db "inefficient" to users and our DBA (business reality)

The impeccable support you guys provide has actually allowed me to complete this project. But to learn that core MS apps are incompatible is tought to swallow. Are there no workarounds I can take? Different file format, creating "mock" rows atop, anything....just to insure that basic formatting holds?

Is there anything I can do??? If not by non-programming methods, do you believe your VB suggestion might retain formats? If so, would you mind adding the basic commands that precede and follow the code you offered

As you can deduce, this issue is handicapping processes we've put in place across teams. Anything...anything you can suggest would be so appreciated

Thanks

 

[John Nurick]

Pete,

I can't understand what you are doing. I've just created a new empty
database and created a table in it, setting field names and data types
to match a table in an existing Excel worksheet. I also set various
field properties such as lengths, default values, etc.

I then imported that table (i.e. the relevant range in the worksheet) to
the existing Access table. The import went fine and the field properties
were not affected.

If however I import the Excel table to a *new* Access table, the fields
created in this new table have data types inferred from the contents of
the Excel table, but their other properties are defaults. This normal:
Access does not try to import Excel cell properties to Access field
properties.

One thing that is often necessary or useful in getting data from Excel
is to import the data to a new, temporary, Access table and then use an
append query to transfer it to the table where you really want it. this
gives an opportunity to change the field types and make other
adjustments.

If this doesn't clarify the situation for you, please post back with
more information on exactly what you're doing.

Thanks for your suggestion. If I go the VB route, I'll definitely

have to ask some more questions. Also, not sure I understand your
suggestion to "import raw data, and append to table".

If you're suggesting that I import, and then re-set properties,

I've tried that multiple times to no avail. When I re-open the file
after import, all field properties return to defaults. I have likewise
formatted every column of the Excel file, then sorted to make sure
populated rows are atop. Fields re-set to defaults. But still, if it
were to work, we import new records from an Excel file each and every
day. Reformatting 50 fields per day will render the Db "inefficient" to
users and our DBA (business reality).

The impeccable support you guys provide has actually allowed me to

complete this project. But to learn that core MS apps are incompatible
is tought to swallow. Are there no workarounds I can take? Different
file format, creating "mock" rows atop, anything....just to insure that
basic formatting holds?

Is there anything I can do??? If not by non-programming methods,

do you believe your VB suggestion might retain formats? If so, would
you mind adding the basic commands that precede and follow the code you
offered?

As you can deduce, this issue is handicapping processes we've put in

place across teams. Anything...anything you can suggest would be so
appreciated.