CODEWITHSUNDEEP
sqlms-accessms-access-2007vba
Gutanoth
Gutanoth

Reputation: 842

Setting field size (per column) while generating table in Access

I am trying to export my Database as an .dbf by using a VBA script, but the dbf requires the database to have certain values for the column size.

When I leave the columns as they are in Access, I get an error saying 

field will not fit in record

How can I set the column size for each column seperatly? Preferably while generating the table, so I don't have to do it manually everytime i generate a new table with queries

And where do I set them? (in a Query or in SQL?)

Thanks in advance!

Edit:

I have made sure that its the field size value that is giving me the error. I changed all the field size values manually by opening the table in Design View.

So now the second part of my question is becoming more crucial. Wether or not it is possible to set the field size while generating the table.

Edit2:

I am currently using SQL in a query to create the table as followed:

SELECT * INTO DB_Total
FROM Tags_AI_DB;

After the initial DB_Total is made, I use several Insert into queries to add other rows:

INSERT INTO DB_TOTAL
SELECT a.*
FROM Tags_STS_ENA_DB AS a 
LEFT JOIN DB_TOTAL AS b 
ON a.NAME = b.NAME
WHERE b.NAME IS NULL;

If I set the column values in the DB_Total table while generating it with the Select into query, will they still have those values after using the Insert Into queries to insert more rows?

Edit3:

I decided (after a few of your suggestions and some pointers from colleagues, that it would be better to first make my table and afterwards update this table with queries.

However, it seems like I have run into a dead end with Access, this is the code I am using:

CREATE TABLE DB_Total ("NAME" char(79),"TYPE" char(16), "UNIT" char(31), 
"ADDR" char(254), "RAW_ZERO" char(11), "RAW_FULL" char(11), "ENG_ZERO" char(11), 
"ENG_FULL" char(11), "ENG_UNIT" char(8), "FORMAT" char(11), "COMMENT" char(254), 
"EDITCODE" char(8), "LINKED" char(1), "OID" char(10), "REF1" char(11), "REF2" char(11),
"DEADBAND" char(11), "CUSTOM" char(128), "TAGGENLINK" char(32), "CLUSTER" char(16), 
"EQUIP" char(254), "ITEM" char(63), "HISTORIAN" char(6), 
"CUSTOM1" char(254), "CUSTOM2" char(254), "CUSTOM3" char(254), "CUSTOM4" char(254), 
"CUSTOM5" char(254), "CUSTOM6" char(254), "CUSTOM7" char(254), "CUSTOM8" char(254))

These are all the columns required for me to make a DBF file that is accepted by the application we are using it with.

You'll understand my sadness when this generated the following error:

Record is too large

Is there anything I can do to make this table work?

Upvotes: 1

Views: 6808

Answers (2)

twoleggedhorse
twoleggedhorse

Reputation: 5048

UPDATE

The maximum record size for Access 2007 is around 2kB (someone will no doubt correct that value) When you create CHAR(255) it will use 255 bytes of space regardless as to what is in the field.
By contrast, VARCHARs do not use up space (only enough to define them) until you put something in the field, they grow dynamically.

Changing the CHAR(x)s to VARCHAR(x)s you will shrink the length of your table to within permitted values. Be aware that you may come into trouble if the row you are trying to insert is larger than the 2kB limit.

Previous

The way to specify column lengths when generating the table is to use a CREATE TABLE statement instead of a SELECT * INTO.

CREATE TABLE DB_Total
(
 Column1Name NVARCHAR(255) --Use whatever datatype and length you need
,Column2Name NUMERIC(18,0) --Use whatever datatype and length you need
,...
) ;

INSERT INTO DB_Total
....

If you use a SELECT * INTO statement, SQL will use whatever field lengths and types it finds in the existing data.

It is also better practice to list the column names in your insert statement, so instead of 

INSERT INTO DB_TOTAL
SELECT a.*

You should put:

INSERT INTO DB_Total
       (
        Column1Name
       ,Column2Name
       ,...
       )
SELECT  a.Column1Name
       ,a.Column2Name
       ,...
FROM   ...
WHERE  ... ;

Upvotes: 1

HansUp
HansUp

Reputation: 97100

In Edit2, you indicated your process starts with a "make table" (SELECT INTO) query which creates DB_Total and loads it with data from Tags_AI_DB. Then you run a series of "append" (INSERT) queries to add data from other tables.

Now your problem is that you need specific field size settings for DB_Total, but it is impossible to define those sizes with a "make table" query.

I think you should create DB_Total one time and set the field sizes as you wish. Do that manually with the table in Design View, or execute a CREATE TABLE statement if you prefer.

Then forget about the "make table" query and use only "append" queries to add the data.

If the issue is that this is a recurring operation and you want to discard previous data before importing the new, execute DELETE FROM DB_Total instead of DROP TABLE DB_Total. That will allow you to preserve the structure of the (now empty) DB_Total table so you needn't fiddle with setting the field sizes again.

Seems to me the only potential issue then might be if the structure of the source tables changes. If that happens, revise the structure of DB_Total so that it's compatible again.

Upvotes: 1

Related Questions