创建表-表的动态名称 [英] Create table - dynamic name of table

问题描述

在Access中创建查询时遇到了一些问题. 我想要一个查询，该查询运行后将询问表Buyers中新记录的名称和姓氏，然后为此买家创建新表(例如，对于John Smith-称为SmiJoh-姓氏中的3个字母，Name中的3个字母).

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).

有什么想法可以在不使用VBA的情况下仅使用SQL来实现吗?

Any ideas how to do that without VBA, using only SQL?

PS.如果没有机会使用SQL来完成所有这些操作，是否有可能只查询创建具有该名称的表?

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?

推荐答案

您问是否可以在Access中使用SQL进行类似的操作，其中table_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);

否，Access的数据库引擎将不允许您使用参数作为表名.

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.

使用一个表格保存所有买家的数据.包括一个用于识别买方的字段.然后使用一个查询，该查询仅检索buyer_id字段与当前用户的buyer_id匹配的行.用该查询作为记录源来构建一个表单.这是一个示例表，其中uname字段包含Windows帐户名.

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

使用Dev Ashish的fOSUserName()函数(获取登录名)，查询仅返回uname与我的Windows用户名(汉斯)匹配的行.

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();

我基于该查询创建了一个表单，该表单包含一个文本框，该文本框在其属性表的数据"选项卡上与这些属性绑定在一起:默认值= 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.

如果您设置了需要MDB格式db的ULS(用户级安全性)，则类似的方法也可以工作.较新的ACCDB db格式不支持此功能.在这种情况下，VBA CurrentUser()函数将返回访问安全用户名的名称.相应地更改查询:

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();

请注意，如果没有ULS，CurrentUser()将为您提供默认用户帐户Admin的名称.

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

最后考虑您的安全要求.使用Access的Jet/ACE进行数据存储将为合作用户提供指导.但是，无论您是否采用ULS，都不能绝对阻止用户查看任何数据.如果您的安全要求更加严格，则将数据存储移至客户端服务器数据库(例如SQL Server).通过将到服务器表的ODBC链接替换为现有的本机Jet/ACE表，您仍然可以将Access应用程序用作前端.

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.

这篇关于创建表-表的动态名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！