Create Table SQL not working

[Gil, Not the Fish]

I am doing calculations that have many different solutions and I would like
to save the solutions to different tables. It is unknown how many solutions
there will be. I wanted to create a table for each solution (216 entries
each) so I put this code in after a solution was reached.
intSolutNum=intSolutNum+1
strSolutNum=Str(SolutNum)
strFileName="Solution" & strSolutNum
strSQL="Create Table " & strFileName & " (Myfield Number)"

The code works to create a table if all I have is "Solution" for the Table
Name but as soon as I try to add the Number to the filename it gives me a
syntax error. Is there some other format for to add a number after each
table name. Or is there another way to go at the whole thing?? Thanx for
any help

 

[Jeff Boyce]

Gil

Building multiple tables, with "meaning" embedded in the table names, is
rarely a workable solution in a relational database. Perhaps if you explain
a bit more what you are trying to do, with an example, folks in the
newsgroup could offer alternate approaches.

Good luck

Jeff Boyce
<Access MVP>

 

[Gil, Not the Fish]

My wife is into quilts and she asked me to write a program to get ALL
possibilities for combining certain patterns - it's not a 'relational
database' - I just plug in some parameters and let it go. When a solution is
found I want to save it in a table as 'Quilt1' and then it continues to
calculate a new solution and I want to save that in table 'Quilt2', etc.
Then when all solutions (unknown) are done I can review and print them by
number. I know this may not be the best environment to do this type of thing
but I think that the problem I'm having is completely separate from the
application itself. Namely, how to concatenate the number to the
'MeaningfulName' part of the filename so that the 'Create Table' SQL string
accepts it. Like I said, I can create a table called 'Solutions' but not
after I have concatenated a
Str(Number) to it- I get a syntax error - try it!! I'm using 2003, by the
way, and I chose this program just because I'm writing databases for my work
right now and I thought I could use the experience. Thanx

 

[Jeff Boyce]

Gil

Access is a relational database (tool), so if you are using Access, ...

I don't understand what it is about "a solution" that requires an entire
table named "Quilt1" or "Quilt99" to save. Again, in most cases, if there
are facts about something (a quilt design?) that can be saved in fields in a
table, you could typically add one more field to the table to store the
"name".

If I had to guess (and I do have to, you haven't described what data is
being stored), I'd guess that you are storing something like color values at
row/column intersections.

If you provide more information, the newgroup may be able to offer more
suggestions...

 

[Tim Ferguson]

Namely, how to concatenate the number to the
'MeaningfulName' part of the filename so that the 'Create Table' SQL
string accepts it.

I am with Jeff on this: it sounds less and less like a project with which
Access's strengths would be helpful (i.e. security, mapping of complex
data, rapid and easy forms design).

In general, it's a big red warning for putting data into a table name. The
meaningful name should be a field on the OneBigTable...

All the best


Tim F

 

[Gil, Not the Fish]

I figured out the solution to my problem and I thought I would post it in
case anybody else has the same problem. Tim & Jeff seemed to be so
interested in my application ( which was running perfectly ) that they
couldn't listen to the problem I was having. The problem I had
concatenating the Number (converted to a string) to the end of a constant
strFileName was that the function Str() adds a space for the sign of the
number which when concatenated to the constant and included in the 'Create
Table' SQL statement must have put a character that the strSQL couldn't
accept so I would get a syntax error although everything seemed correct. .
So, after I made the Number a string I then took the sign away by using
Mid(Str(Number),2) and it returned ONLY the number part so I could create the
tables called Quilt1, Quilt2, Quilt3, etc. In order for this to make sense I
think you would have to go back and read the rest of the threads. I hope
this helps someone.
Thanx

 

[Jeff Boyce]

Gil

Only speaking for myself, I understood quite well what you were asking. I
chose not to offer "how to" suggestions because of the possibility that you
were trying to "drive nails with a chainsaw". Just because it is possible
to do something doesn't necessarily make it a good idea.

I am NOT saying that what you're doing is not a good idea. I AM saying that
I don't understand the underlying rational or data structure well enough to
offer suggestions.

Sorry if you took that to be obstructionist -- I was seeking clarification.

Best of luck on your project

Jeff Boyce
<Access MVP>