Make-Table Trouble

[Sasquatch]

Pertaining to Access XP (2002):

I've noticed that when you run a make-table query it does two annoying
things that have the potential to cause problems.

One is, it sets the "Subdatasheet Name" property to [Auto]. I've read that
this can have serious performance issues (http://allenbrowne.com/bug-09.html)
so I'm wondering if there is a simple way to turn this off. Changing the
property manually doesn't work because the next time the query runs it
deletes the table and remakes it fresh.

The other thing I noticed is if you use the Access function
StrConv(strCompanyName, 3) -- which will convert our upper case CompanyNames
to Proper Case -- then it creates the field in the table as a string with 255
characters. The problem here is the query has alot of strings that its doing
this to and the table ends up with records that would be nearly 19000
characters wide, whereas the limit is around 4000. If all the fields held
their maximum data then the record will fail (again, thanks to Allen Browne
for this tid-bit). So, is there a function I can use to wrap around each
StrConv( ) funtion call to limit the number of characters set aside for each
field in the table? For example, I tried Left(StrConv(strEEName, 3), 50) to
only set aside 50 characters but it still created the field with 255
characters.

Thanks in advance,
Sasquatch

 

[Marshall Barton]

Pertaining to Access XP (2002):

I've noticed that when you run a make-table query it does two annoying
things that have the potential to cause problems.

One is, it sets the "Subdatasheet Name" property to [Auto]. I've read that
this can have serious performance issues (http://allenbrowne.com/bug-09.html)
so I'm wondering if there is a simple way to turn this off. Changing the
property manually doesn't work because the next time the query runs it
deletes the table and remakes it fresh.

The other thing I noticed is if you use the Access function
StrConv(strCompanyName, 3) -- which will convert our upper case CompanyNames
to Proper Case -- then it creates the field in the table as a string with 255
characters. The problem here is the query has alot of strings that its doing
this to and the table ends up with records that would be nearly 19000
characters wide, whereas the limit is around 4000. If all the fields held
their maximum data then the record will fail (again, thanks to Allen Browne
for this tid-bit). So, is there a function I can use to wrap around each
StrConv( ) funtion call to limit the number of characters set aside for each
field in the table? For example, I tried Left(StrConv(strEEName, 3), 50) to
only set aside 50 characters but it still created the field with 255
characters.

First, make table queries are rarely, if ever, needed.
Revaluate what you are doing to see if you can do the job
without using them and then your question becomes moot.

Second, I you really do have one of those rare situations,
you should consider using a precreated table with the
desired field properties. Then you can use a Delete query
to empty the table, followed by an Append query to add the
data.