'Insert Into' with field size

[normajean]

Hi:
I am creating a temp table in an Access 2000 database (from a pass-through SQL query). Its all working groovy, except that all my text fields are 255 characters.
Is there a way to specify the length for various Text fields created with the 'Select into' statement (without having to go back and alter the table once its created)?
I've looked everywhere I can think of on the web and can't find any information, so I'd appreciate your insights and experience!
Thanks,
Normajean

 

[FoFa]

Nope, but on the other hand, they are not actually 255, Access uses them as varchar in Sql, or just what they contain. So think of it like if it has 10 characters in it, it is 10 characters long. Usually this not a problem. Of course, you can setup a table and just import into the existing table.

 

[pdx_man]

I just want to get this straight. You have a Stored Procedure on the SQL Server (or just passing the code to SQL Server) that you are executing through a Pass-Through query creating a table on the SQL Server that you are linking to in Access 2000?

Use the Convert Statement to specify field size.

SELECT CONVERT(VARCHAR(8),yt.Field) as NewField
INTO YourTempTable
FROM YourTable yt

 

[normajean]

Hi PDX Man!
I am creating the table in the local Access database. The source of the 'select into' is a querydef that I build just prior to the 'Select into'. The querydef is a pass-through that pulls data from multiple HUGE tables over a network (performance issues are why I create a temporary table in the local database).
Every morning I pull new data over from the updated SQL Warehouse.
I can't really keep the table structures and just fill with new daily data, because there are multiple users on each PC desktop, and the table's name will vary depending on who is logged in, the option they have selected, and the date. I clean up the tables when the user logs off.
My question came from a concern of bloating my Access database with tables that are larger than they need to be. Does the memory used for a table def depend on the size of the fields defined, or on the actual data in the table? Also I'm concerned with query performance (forms built on these tables allow the user to apply filters using these text fields, and I'm concerned that these operations will not work as efficiently on 255 size text fields as on 5 size text fields.)
Also, if I am daily creating and dropping tables, do I need to Compact daily to clean up memory? (I have heard Access didn't used to do this well - is that true? If so, can this be done programmatically?
Thanks, NJ

 

[normajean]

OOPS! Forgot to include in my last post:
I tried using the CONVERT in my passthrough SQL query Select list to specify that I only wanted it to return a char(5) for one of the text fields, but the Insert Into still created a text 255 field for the item in the table.
I thought of that also, but was disappointed to see it didn't.
NJ

 

[pdx_man]

Well, INSERT INTO is for an APPEND query to an existing table structure ... so I'm wondering how this is all actually set up.

Do you have a Pass-Through query that is just doing the SELECT from the HUGE tables, then run a Make-Table query in Access based on this Pass-Through query, then run an Append query in Access?
Please post your Pass-Through query.

Yes, you will want to compact the DB often.

Do you have CREATE permissions on the back-end DB where you can create a table there? You can then create a Pass-Through query where you dynamically update the parameters for the user, options selected and dates in the Pass-Through query.

Bottom line, there may be better ways to create the structure here.

 

[normajean]

Hey PDX_Hombre!
Yes, you are correct; my Pass-Through query is just doing the SELECT from the HUGE tables, then I create another querydef, assign the querydef.sql = 'Insert into NEWTABLE select * from qryPassThrough', and run that query. The 'Insert Into' creates the new table, and doesn't mind that it doesn't exist.

I'd like to post my actual Pass-Through query, so you could see it, but I won't be back at work till Monday.

I wish I did have CREATE permissions on the SQL Warehouse, but I don't.

What do you think about needing to adjust the text field sizes? Do you think that it makes a difference in memory size and query/filtering/sorting perfomance?
If you think field size does make a difference, do you know if I can use ALTER TABLE to change field sizes? I can't find any examples of where someone has done this - only examples of adding new columns or indexes, etc.

Re: the compacting, I found some infor on the web that in Access 2000 only, if you want to automatically compact the database each time it's closed, select the Compact On Close option available under Tools | Options | General tab. I'll set that option for my database.

Cheers, Normajean

 

[RV]

>do you know if I can use ALTER TABLE to change field sizes?<

In Access 2000, you can use the ALTER COLUMN to change the data type of an existing field.

RV

 

[FoFa]

The 255 characters is just the max size a column can get, the columns are not "padded" out with spaces or anything. If there is not that much data, it doesn't use the space. Typically Access will use space, and later try to recover that space again (reuse). I have found in a case like yours, the DB will grow to a certain size, than just remain stagnant pretty much as it can reuse the deleted space. So if you compact it, and it grows, compact it and it grows, what are you gaining besides a bunch of work, why not let it grow until it reaches it's stagnant size and let it be? Of course one has to ask what you are transfering that much iniformation for anyway into Access? If it truley is that much data, you must be summarizing it because the detail would to much for the average human. So why not do your summary on the Sqlserver and return the summarized data instead? Have you considered a Stored Procedure maybe on the Sqlserver side you can pass parameters into to return just what you want? Might give you better performance over all also.

 

[Pat Hartman]

just remain stagnant pretty much as it can reuse the deleted space.

- Jet does NOT reuse space. Only a compact will recover unused space.

I would create a table defined as required and then append the data to it rather than creating a new table. That way you won't have to fix up the table after it is made. Deleting and appending is no worse than overlaying an existing table. In either case, the space occupied by the data is doubled and can only be recovered by compacting.

 

[FoFa]

I must admit beyond RECLAIMING unused space (which is not the same thing as reusing) I have not seen anything on this issue (like a MS white paper). However I have 5 or 6 access applications and to use one as an example, it is 18 Mb in size. Every week the user deletes/imports/processes an (approx) 8 mb file. Yet this DB has not gained a single byte in over a year (ok maybe 1 or 2 but you get my drift). If jet does not reuse space, I would think it would grow by 8Mb every week. So no MS document to backup it up, but an interesting observation on my part.

 

[FoFa]

Ok, I did a little research and found out that most people confuse reclaiming space and reusing space in Access. Reclaiming space would be what happens if you compact the DB. Reusing the space is when Access over writes a section of the DB that has been emptied. Here is what I found (This pertains to Access 95/97, I didn't find anything on 2K and newer). Access will mark as FREE a 2K PAGE that has nothing in it. This FREE LIST is scanned to see where Access may write data to in the current DB, and if no FREE pages exist, it expands the DB to create new 2K Pages for use. If there is anything in a 2K page, it is NOT considered FREE. FREE pages are NOT released to the OS (that is why a DB does not Shrink unless you compact it).

 

[normajean]

I now understand!

Dear FoFa, pdx_man, RV and Pat Hartman:
Thank you for helping me out on this! I really learned alot about database storage from your posts; although I tried to seach the web, I couldn't seem to locate the information I was looking for. So glad to have this Forum!
Re: the data pulled over, yes I am only pulling summary data via a pass-through query. Unfortunately, I don't have permission to create stored procedures on the SQL server warehouse, or I would definitely move some of the development over there.
Thanks again! Normajean