Difficulty with the CREATE TABLE function using VB on a CMD button (1 Viewer)

[Jabell]

Hello,

I have a CMD button which I want to use to create a temporary table with the records from selected results in a list box but I am having trouble creating my table using the Creat table funtion as shown below

Private Sub CMDedit_details_Click()

CurrentProject.Connection.Execute "CREATE TABLE edit (id INT, company CHAR, address1 CHAR, address2 CHAR, town CHAR, county CHAR, pcode CHAR, telephone CHAR);"

When I try to create the table with more than the 8 columns shown here, the debuger tells me the Record is too large.

Are there limitations when creating a table, to how many columns you can have?

My aim is to create a temporary table with selected results which can be used in a subform to edit information, then I can save the changes to the data in the origonal table. This means I need around 12 columns, but I can't seem to create more thn 8.

Any help is greatly appreciated.

 

[Jack Cowley]

Hmmm. Why do you need to create a temporary table? Can't you use a query as the Record Source for your subform? It just seems like you are going to more work than you need to....

Jack

 

[Pat Hartman]

Records are limited to 2k in length. How many characters are being defined for each of the CHAR fields? If it is 255, you can run out of room in a hurry.

 

[Jabell]

Ever come to a point where you can either, scrap your original idea and do something a different way that is more simple and ultimately better?

My form started as just a front page to look at my data but then I was told my boss wanted to be able to edit all the info so i wabted to export the data from the pretty list box into a new sub form which could then edit the info and resave it over the old info using the PK as the link...... I know what your thinking, why not just use a subform to show the data in the main form to begin with..... well I did and now it works, to be honest I was just a little afraid of subforms!

thanks for the replies