Categories
askquestion

How to copy a table from one database to other database?

How to copy a table from one database to other database?

Ask Question

Asked
10 years, 6 months ago

Active
10 years, 6 months ago

Viewed
3k times

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{
margin-bottom:0;
}

0

Using VB 6 and Access 2003

I want to copy a table from one database to other database.

Database1

Table1
Table2

Database2

Table3

Above, I want to copy the Table3 to Database-1

Expected Output

Table1
Table2
Table3

How to write a code?

Need VB6 Code Help.

ms-access vb6

share|improve this question

edited Sep 2 ’09 at 4:52

OMG Ponies

280k6767 gold badges473473 silver badges474474 bronze badges

asked Sep 2 ’09 at 4:49

GopalGopal

10.2k4545 gold badges137137 silver badges223223 bronze badges

You don’t mention if you need just the table schema to be copied or the table schema and its data as well.

– Renaud Bompuis
Sep 3 ’09 at 10:56

add a comment
 | 

2 Answers
2

Active

Oldest

Votes

4

Using ADOX to copy the structure of the data would probably be the easiest way.

Dim sourceCat As New ADOX.Catalog
Dim targetCat As New ADOX.Catalog

Set sourceCat.ActiveConnection = connSource
targetCat.ActiveConnection = connTarget

Dim sourceTable As ADOX.Table
Set sourceTable = sourceCat.Tables(“TableName”)

Dim newTable As New ADOX.Table
Set newTable.ParentCatalog = targetCat
newTable.Name = sourceTable.Name

Dim sourceCol As ADOX.Column
Dim newCol As ADOX.Column

For Each sourceCol In sourceTable.Columns
Set newCol = New ADOX.Column
newCol.Name = sourceCol.Name
newCol.Type = sourceCol.Type
newCol.DefinedSize = sourceCol.DefinedSize
newCol.ParentCatalog = targetCat

newTable.Columns.Append newCol
Next sourceCol

targetCat.Tables.Append newTable

This is a fairly basic example, it ignores all indexes
and column properties (such as autoincrement).

A much more complete example can be found here.

share|improve this answer

answered Sep 2 ’09 at 8:51

jakdepjakdep

74911 gold badge99 silver badges2424 bronze badges

Note that you’d have to add a reference to ADOX.

– Tony Toews
Sep 2 ’09 at 19:01

add a comment
 | 

0

Be aware that you cannot be sure you have extracted all of a table’s schema even when using both ADO (which you need for CHECK constraints, WITH COMPRESSION, etc) and ACEDAO (which you need for complex data types, etc).

Here’s an example of such a table:

Sub CantGetCheck()

On Error Resume Next
Kill Environ$(“temp”) & “DropMe.mdb”
On Error GoTo 0

Dim cat
Set cat = CreateObject(“ADOX.Catalog”)

With cat

.Create _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & _
Environ$(“temp”) & “DropMe.mdb”

With .ActiveConnection

Dim Sql As String
Sql = _
“CREATE TABLE Test ” & _
“(” & _
” data_col INTEGER NOT NULL, ” & _
” CONSTRAINT data_col__be_positive ” & _
” CHECK (data_col >= 0), ” & _
” CONSTRAINT data_col__values ” & _
” CHECK ( ” & _
” data_col = 0 OR data_col = 1 OR data_col = 2 ” & _
” OR data_col = 3 OR data_col = 4 OR data_col = 5 ” & _
” OR data_col = 6 OR data_col = 7 OR data_col = 8 ” & _
” OR data_col = 9 OR data_col = 10 OR data_col = 11 ” & _
” OR data_col = 12 OR data_col = 13 OR data_col = 14 ” & _
” OR data_col = 15 OR data_col = 16 OR data_col = 17 ” & _
” OR data_col = 18 OR data_col = 19 OR data_col = 20 ” & _
” ) ” & _
“);”
.Execute Sql

Dim rs

‘ 5 = adSchemaCheckConstraints
Set rs = .OpenSchema(5)

MsgBox rs.GetString

End With

Set .ActiveConnection = Nothing
End With

End Sub

The output shows that while the definition for the constraint named data_col__be_positive can indeed be extracted, the data_col__values definition cannot (because it exceeds 255 characters).

So really the solution is to always retain the code you used to create and subsequently alter the table. For me, using SQL DDL scripts for the purpose make a lot of sense (I do not need the few features that are not creatable via DDL).

share|improve this answer

answered Sep 3 ’09 at 13:07

onedaywhenonedaywhen

47.6k1212 gold badges8484 silver badges127127 bronze badges

add a comment
 | 

Your Answer

Thanks for contributing an answer to Stack Overflow!Please be sure to answer the question. Provide details and share your research!But avoid …Asking for help, clarification, or responding to other answers.Making statements based on opinion; back them up with references or personal experience.To learn more, see our tips on writing great answers.

Draft saved
Draft discarded

Sign up or log in

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Submit

Post as a guest

Name

Email
Required, but never shown

Post Your Answer

Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you’re looking for? Browse other questions tagged ms-access vb6 or ask your own question.

The Overflow Blog

Defending yourself against coronavirus scams

Podcast Episode 220: Fully Remote

Featured on Meta

Planned maintenance scheduled for Saturday, March 28, 2020 at 13:00 UTC (9AM…

An Update On Creative Commons Licensing

Community and Moderator guidelines for escalating issues via new response…

How does the Triage queue work?

Triage needs to be fixed urgently, and users need to be notified upon…

Visit chat

Related

0How to display a non matching value also?1How to join a two tables compare with date?0Add CSV to Access database from Excel0Join Multiple Tables from Different Months0updating one table with the value of another table and the two tables are in seperate database0How to populate a field with a foreign key using 2 linked tables with SQL?0A program to synchronize the objects of two databases MS Access1Update query using a field from another Access database0Select multiple values from one table1, find in table2 and insert in table3

Hot Network Questions

When I burn isopropyl alcohol (IPA), it burns orange. But when I burn ethyl alcohol, it burns totally blue. Why is this?

Can I safely plug a two prong plug into an extension cord with a ground socket?

What is this object on the tail of an F-16?

How does FM station have fixed frequency when FM modulation changes the frequency?

Why would a ‘first world’ alien opt to have a brood of dumb children rather than one smart one?

How much of the US media referred to the COVID-19 as the “Wuhan Virus” until it received an official designation from the WHO?

Can I substitute salts 1:1 by weight?

Methods of permanently joining a metal mask to a face

Do planets orbiting stars emit gravitational waves?

I have a YMA Visa – will Germany let me into the country despite COVID-19?

What is this fence-like feature on colonial roofs?

Will Canadian citizens be denied entry to Canada during COVID-19 border closure?

Questions about existence of injections between infinite sets and the sets of all infinite topologies on them

Can a wormhole be created if it has not always existed?

Do higher mayhem levels give better loot, or only more of it?

Did French police fine homeless persons for not being confined at home?

What is the point of this resistor network?

Broadcasting to unknown Class C Network

What are the objections to Mitch McConnell’s COVID-19 Bill?

Why do assistant type characters tend to have British accents in movies?

What is the geometric shape of the Monster sporadic group?

Autoroute or not?

What is a likely cause of this glow in my photos?

American production of /ɾ/ in other languages

more hot questions

Question feed

Subscribe to RSS

Question feed
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb

Leave a Reply

Your email address will not be published. Required fields are marked *