Create table from Listbox

[psbsmms]

I have a master item table that i am tryong to set up a listbox that once selected all the items would be moved to a new table with all the fields of the master table. What i am currently doing is copying the master table with a new name to get the structure and then deleteing the items within the table. I can get the copy going with the new name, but when I run the delete sql it deletes the master table not the copy here is the code.

Private Sub Command4_Click()
Dim strTableName As String
Dim strSQL As String

Stop
If IsNull(strTableName = CmbCustomer.Column(1)) Then
MsgBox "You have not selected a customer, Select correct Customer and Try again", vbOKOnly
Exit Sub
End If

strTableName = CmbCustomer.Column(1)
strTableName = "tbl" & strTableName

DoCmd.CopyObject , strTableName, acTable, "tblitems"

strSQL = "DELETE *.* FROM [" & strTableName & "]"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

 

[formerTexan]

Hi,

Rather than copy an existing table and then delete the data, it will be a bit less cumbersome and slightly less prone to inevitable database bloat to just create a new table structure.

---
Dim strSQL As String
strSQL = "CREATE TABLE " & " tblTABLE " & _
" (fld1 Text, fld2 Number);"

DoCmd.RunSQL strSQL
---

It sounds like you (or your users) are going to be creating reams of tables, so instead of hardcoding the table name, set up a string variable for the name and sort out some mechanism for creating a unique table name for each table creation.

That said, I am curious as to why you feel you need to create a new table in the first place rather than keep all the data in a single table. There are many ways to designate a subset of data without shunting it off to a new table.

CHeers, Bill

 

[psbsmms]

i was trying to avoid creating the table structure because I hate to type, and there are alot of fields in the table. But really I only have to do it once so I should have done that in the first place. i understand your comments and I have struggleed with doing it this way. What it is we are a catalog operation and I have a master list of items we sell, we were selling to only one big customer, but now we are branching out to more. My problem is that each customer will have 1. Unique items just to them, 2. Several will share items, 3 and some items are used with all customers. To complicate it alittle more there are separate pricing for each customer, as well as volume price breaks. I also have to plan for the inevitable price increases that will cascade down to the customer tables. We will only have separate tables for the largest of customers so we won't be making scads of tables, but I wanted to be able to programmably create the separate tables by the users so I don't have to keep going back and changing code, or adding fields to the master list each time we gain a new customer. We are small and I run the office and warehouse as well as program so my time is limited and until I can show the boss we are making $$ I can't hire out the programming.

 

[MajP]

This is a bad idea, you are asking for problems by copying like information. It is a relational database. Set up a many to many relationship between customers and items.

[/quote]
My problem is that each customer will have 1. Unique items just to them, 2. Several will share items, 3 and some items are used with all customers.
[/quote]

This is not a problem, but a normal relationship.

joinTblCustomers_Items
customerID
itemID
otherUniqueFields to a customer item relationship

ex
CustomerID ItemID
1 22
1 27
1 29
2 44
2 27
2 29
3 27

Item 27 shared by all
Item 22 unique to customer 1
Item 29 shared by 1 and 2

 

[psbsmms]

Thanks for your input, and I am thinking of the relationships, but we also have to keep in mind this is an Access DB and not SQL, sometimes to keep the speed for users up we need to duplicate some data and i still have to worry about the differnt prices for different customers.

 

[MajP]

Unique price can easily be handled as

CustomerID ItemID curCustomerUniquePrice
1 22
1 27
1 29 $300.00
2 44
2 27
2 29 $100.00
3 27 $40.00

Customer 1 pays 300 for item 29, customer 2 pays 100
Customer 3 pays 40 for item 27, customer 1 pays the standard price.

keep in mind this is an Access DB and not SQL, sometimes to keep the speed for users we need to duplicate some data

Maybe, but that seems unlikely. How many records in the Master list? The queries to the join table would be simple. If speed was a problem then build temp tables based on the above data structure.

 

[formerTexan]

Now that you have explained the situation in more detail, I can only suggest that you go with MajP's table structure rather than create new tables. Getting the table structure correct should take precedence over other design issues. It is invariably worth the little bit of extra time up front and pays huge dividends later

The good news is as MajP said, speed is highly unlikely to be an issue unless you are thinking in terms of 100's of thousands of customers and items.

It sounds as if this is a database that is and will continue to grow in terms of its use. Having data for each customer divided into seperate tables will result in programming difficulties. A couple of examples to consider:

1. Each time an item is introduced or changed or withdrawn, every customer table will have to be seperately queried and updated
2. To provide summary data on multiple customers you will need to either programmatically combine customer tables into a single table or use Union queries. Changes in the code or queries will be required everytime a new customer is added. Then you will begin to see speed as an issue.

Cheers,
Bill