Solved

Duplicate an Access table entirely through code

Posted on 2004-03-23
8
189 Views
Last Modified: 2013-12-24
Is there an easy way with Cold Fusion/SQL to copy a table's structure and datatypes, but not the information, to a new table with a new name?

Thank you!

Jim Vosika
http://www.wallclocksrus.com
0
Comment
Question by:jvosika
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Expert Comment

by:sandy12879
ID: 10657748
select *
into <other table name>
from <tablename>
where  condition ......

make sure that th option 'select into/bulkinsert is enabled.  Which you can do like this:

exec sp_dboption <databasename>, 'select into/bulkcopy', true
0
 
LVL 4

Expert Comment

by:sandy12879
ID: 10657822
Or may be u can try this also

create table New_table  as select * from Old_Table where  condition.....

Please let me know


Regards
Sandy
0
 

Author Comment

by:jvosika
ID: 10657907
I tried both options and neither did it. For the second one here is my code:

<cfquery datasource="dbTest" name="createtable">
create table 12345 as select * from blank where id=1
</cfquery>

...and here is the error I get:

Error Occurred While Processing Request  
Error Executing Database Query.  
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.  
 
1 : <cfinclude template="header.cfm">
2 : <cfoutput>
3 : <cfquery datasource="dbTest" name="createtable">
4 : create table 12345 as (select * from blank where id=1)
5 : </cfquery>

 
 
0
 
LVL 17

Expert Comment

by:anandkp
ID: 10657998
try DTS in SQL - it shld do the job for u !
0
Give your grad a cloud of their own!

With up to 8TB of storage, give your favorite graduate their own personal cloud to centralize all their photos, videos and music in one safe place. They can save, sync and share all their stuff, and automatic photo backup helps free up space on their smartphone and tablet.

 
LVL 35

Expert Comment

by:mrichmon
ID: 10658745
One thing you need to verify is in the CFAdmin make sure that you have the permissions set in your datasource to allow the creation of tables otherwise it will never work.
0
 
LVL 5

Accepted Solution

by:
jayeshshah earned 500 total points
ID: 10660140
try this sql

<cfquery datasource="dbTest" name="createtable">
    select * into 12345 from blank  where 1<>1
</cfquery>


Regards

JBS
0
 
LVL 5

Expert Comment

by:jayeshshah
ID: 10660151
this shall just clone the table without any data. :-)
0
 

Author Comment

by:jvosika
ID: 10668960
Perfect! That did the trick. Thank you all for your help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now