Avatar of FaheemAhmadGul
FaheemAhmadGul
Flag for United Kingdom of Great Britain and Northern Ireland asked on

An SQL query which will copy the schema only of a TableA in Access Data based and create a new TableB in the same database

I need help with a SQL query for an Access Database which will copy the schema only of a give table (named TableA) and will create a new table in the same Access Database with the name TableB. The query should not copy Data from TableA but only create a new TableB which has the structure as TableA

SQLDatabases* Access 2019Microsoft Access

Avatar of undefined
Last Comment
Jagdish Devaku

8/22/2022 - Mon
slightwv (䄆 Netminder)

To create a table with all the same columns but no data:
select * into tableB 
from tableA where 1=2;

Open in new window

ASKER CERTIFIED SOLUTION
Flyster

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Edwards

You need to decide if you want to use a query to copy a table or vba to copy a table,  They are two different things.
You asked for a query, but I'm assuming you want the query to also include all indexes, primary keys, autonumber fields, (attachment fields, if any?), etc. i.e. a complete copy of the table.
There is no Access query that will create an exact copy of the table except a "Make Table" DDL query and you have to manually create that.  But the "Make Table" Access query feature will get you close, but it doesn't copy primary key, indexes, etc.
https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s15.html
There is the vba "CopyObject" function that does the job of the copy-and-paste feature if that's what you are looking for.
The link below covers just about all methods:
https://www.geeksengine.com/article/duplicate-access-table.html

ste5an

As @Mark already pointed out, it is really hard to create an exact copy of an table or object in Access.

Thus: What is your use-case?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Jagdish Devaku

Hi,
Simple VB script with SQL Code might help. (SELECT.INTO statement (Microsoft Access SQL))
Please refer to the below link for more details.
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/select-into-statement-microsoft-access-sql