Create table - dynamic name of table

I've got some problem with creating a query in Access. I'd like to have a query, which after running will ask about name and surname of new record in table Buyers, and then create new table for this buyer(e.g. for John Smith - called SmiJoh - 3 letters from Surname and 3 from Name).

Any ideas how to do that without VBA, using only S开发者_StackOverflowQL?

PS. If there's no chance to do all of this using SQL is there any possibility to do only query creating a table with this name?

You asked whether you can do something like this with SQL in Access, where table_name is a value you supply when the statement executes.

CREATE TABLE [table_name](
    id COUNTER CONSTRAINT pkey PRIMARY KEY,
    date_field DATETIME);

No, Access' database engine will not allow you to use a parameter for the table name.

But like the comments you've received, I encourage you to re-consider your plan to create a separate table for each buyer. That will be a complicated mess to build and maintain.

Use a single table to hold data from all buyers. Include a field to identify the buyer. Then use a query which retrieves only the rows where the buyer_id field matches the current user's buyer_id. Build a form with that query as its record source. Here is a sample table where the uname field holds the Windows account name.

id   uname time_only
5018  fred 7:00:00 AM
5063  hans 2:00:00 AM
5072  hans 3:00:00 AM

With Dev Ashish's fOSUserName() function (Get Login name), this query returns only the rows where uname matches my Windows user name (hans).

SELECT d.id, d.uname, d.time_only
FROM discardme AS d
WHERE d.uname = fOSUserName();

I created a form based on that query which includes a text box bound to uname with these properties on the Data tab of its property sheet: Default Value =fOSUserName(); and Enabled No. If you don't want the user to even see the uname value, set Visible No on the Format tab.

You would still need to lock down the application to prevent the users from opening the table directly. But that would also be required with your original scheme to create a separate table for each buyer.

A similar approach could work if you have set up ULS (user-level security), which requires an MDB format db; it's not supported in the newer ACCDB db format. In that case the, the VBA CurrentUser() function will return the name of the Access security user name. Change the query accordingly:

SELECT d.id, d.uname, d.time_only
FROM discardme AS d
WHERE d.uname = CurrentUser();

Note that without ULS, CurrentUser() will give you the name of the default user account, Admin.

Finally consider your security requirements. Doing this with Access' Jet/ACE for data storage will amount to offering guidance to cooperative users. However, whether or not you adopt ULS, you can't absolutely prevent a user from viewing any of the data. If your security requirements are more stringent, move the data storage to a client-server database (SQL Server, for example). You can still use your Access application as a front-end by substituting ODBC links to the server tables for the existing native Jet/ACE tables.

Have you tried using an EXEC

DECLARE @Name VARCHAR(6)
SET @Name = 'SMIJOH' --Query your table name
EXEC ('CREATE TABLE ' + @Name + ' (ID INT IDENTITY(1,1), etc....)')