Access 2000 and ADOX - New Table

Access 2000 and ADOX - New Table

Post by H. Kanan » Sun, 25 Feb 2001 23:58:58



.
I want to create a new table, an exact copy of the existing table,
without
data. In Oracle I could do this
by
    Create table tablename as(select * from oldtable where 2=1)
How can I accomplish this in Access 2000 and using ADOX  at runtime.
Thanks.
 
 
 

Access 2000 and ADOX - New Table

Post by Carl Prothma » Mon, 26 Feb 2001 04:20:52



Quote:> .
> I want to create a new table, an exact copy of the existing table,
> without data. In Oracle I could do this by
>     Create table tablename as(select * from oldtable where 2=1)
> How can I accomplish this in Access 2000 and using ADOX  at runtime.

Try something along the lines of
http://www.able-consulting.com/ADOX_Faq.htm#Q3

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

 
 
 

Access 2000 and ADOX - New Table

Post by H. Kanan » Mon, 26 Feb 2001 10:12:19


I am aware of Creating a New table and appending all the columns. But that
is not exact image of the
existing table which I want to copy. Each column's  properties need to be
defined separately. What I want to know is, if there is a short cut SQL
statement that can do this job, like I do in Oracle.
Thanks.
ram



> > .
> > I want to create a new table, an exact copy of the existing table,
> > without data. In Oracle I could do this by
> >     Create table tablename as(select * from oldtable where 2=1)
> > How can I accomplish this in Access 2000 and using ADOX  at runtime.

> Try something along the lines of
> http://www.able-consulting.com/ADOX_Faq.htm#Q3

> --

> Thanks,
> Carl Prothman
> Microsoft Visual Basic MVP
> http://www.able-consulting.com

 
 
 

Access 2000 and ADOX - New Table

Post by Carl Prothma » Mon, 26 Feb 2001 11:14:29



Quote:> I am aware of Creating a New table and appending all the columns. But that
> is not exact image of the
> existing table which I want to copy. Each column's  properties need to be
> defined separately. What I want to know is, if there is a short cut SQL
> statement that can do this job, like I do in Oracle.

Use the SELECT INTO sql command

e.g.
'********
    Dim oConn As ADODB.Connection

    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=.\AdvWorks.mdb;"

    oConn.Execute "SELECT * INTO temp2 FROM Customers WHERE 1=2"
'********

--

Thanks,
Carl Prothman
Microsoft Visual Basic MVP
http://www.able-consulting.com

 
 
 

1. Access 2000 ADOX schema/ Table Field Descriptions

I have serveral Access 2000 databases that I'm importing
into SQL 7 with one problem. I extensively use the schema
object in asp which uses the description column in access
2000 i.e. field_name data_type description.  I have not
found how SQL 7 handles or if it does the "description"
from Access.  I noticed in SQL 7 that you can import the
metadata and see the descriptions associated with Access
field names but I've yet to figure out on how to import
them into SQL7 if it supports it.

Please advise.

2. Advanced Dynamic SQL (at least to me)

3. Vinculate Tables in Access 2000 using ADOX

4. SQL Server 2000 and ODS

5. ADO or ADOX creating a new table from a table template

6. DLL error during install

7. Importing an Access 2000 table into another Access 2000 database with VB Code

8. How to synchronize Two Oracle Databases ?

9. Convert an Access 2000 MDB to ADOX creation code

10. Plateform: ACCESS 2000, ADOX, VC 6.0

11. ADOX, Access 2000 and Jet OLEDB property settings

12. Using ADOX to modify SQLServer 2000 tables

13. Cannot view queries created using ADOX in Access 2000 interface