Make Table Query - Determining Format as Text

[fdormoy]

I want to construct a Make table query to create Table2 from Table1.
However, in creating Table2, I want to add new fields or columns which do not exist in Table1.
I want the new columns to have null values for the time being because I will update these columns at a later stage.
So, after I run the query I look at Table2 in Design View and I noted that the new columns I created are in Binary Format (dont ask me why, since Access do not support this format, but this is fact of life).
So I want to make sure that the new columns I want to create with null values are in text format.
Unfortunately, in the Design View of the query I constructed, in looking at format in Propert Sheet, I see lots of date, currency general numbr etc. format types but no Text format. Why is that ?


So how can I specify for that field that it has to be created in Text format ?
Thanks ina dvacne for your guidance.

[JoeM]

Two things:

1. In these scenarios, I find it works way better to create a blank Table shell, formatting each field EXACTLY the way you like, then using Append Queries to populate it (instead of using Make Table queries). It gives you much more control over the layout and process.

2. You may want to ask the question of whether or not it is necessary to create another table at all (many times it is not). If everything you need can be derived through queries, there is often no need to create a separate table to store it (general rule of thumb in Access: do not store anything which can be calculated from other fields).

[fdormoy]

Well, in fact my question was related to whether or not using this Null code when producing a Field in text File when creating a new table.
I think I solved the problem in avoiding using "Null" since this is what cause the creation of Fields in Binary Format in the table created from that query.
So what I did is, instead of specifying that the Field created in the new table must hall Null value, it is to say that this Field will be with " " which means blank value.
The new table is then showinh Fields in Text format and no more in Binary Format.
So I am wondering what is wrong with the Null value specified in the creation of new fields in the new tables...

[JoeM]

I was suggesting you maybe re-examine the methodology you are trying to implement (try to see the "whole forest", not just a "single tree"). I think you'll avoid a lot of headaches if you try a different approach.

I don't like MakeTable queries, because (as you are experiencing yourself) you have very little control over how the Field Properties are set up (you are kind of at the mercy of how Access thinks it should be done). By creating a blank table (only have to do this one time!), where you set up each and every field exactly the way you like, and then populating it using an Append Query, you can avoid a lot of those issues.

[fdormoy]

Thanks JoeM
You gave me a good idea and I followed your suggestion and it worked fine.

[JoeM]

Great! Glad to hear that it worked out for you!

I find the same frustration when trying to import information directly from an Excel file. Access doesn't let you choose the data types, and tries to "guess" what they are, and sometimes guesses wrong. It can be very frustrating at times!

[fdormoy]

But I have many more frustrations when dealing with Access, in particular i am getting very often the message "System Resource Exeeded" which forces me to Compact-Repair every time I get this message and someties this is not sufficient and I have to exit Access and restart the computer to make it work again. Did you face that problem as well and how did you solve it ?

[JoeM]

How much free space do you have on your computer?
Not sure if your computer processor or RAM could be an issue, if you have an older or not so powerful computer.

How big is your Access database (I believe 2 GB is the maximum database size)?
If there is a lot of data going in and out of your database, you will want to Compact and Repair frequently to keep the size from bloating.

If you continue to have issues, you may have a corrupt database. The way around that is to create a brand new blank database, and then Import all of your Access objects at once from your old database into your new one.