-
Apr 20th, 2017, 10:48 AM
#1
Thread Starter
Fanatic Member
Copy Access table to another database
I wish to copy one table which exists in Access database A (structure and data) to Access database B using VB6 and DAO without doing it record for record.
Any help will be appreciated.
Thanks
-
Apr 20th, 2017, 11:08 AM
#2
Re: Copy Access table to another database
I use
Code:
INSERT INTO Table1
SELECT *
FROM [;database=C:\A.mdb].Table1;
or
Code:
INSERT INTO Table1
SELECT *
FROM Table1 IN 'C:\A.mdb';
edit: That was just data, here's both
Code:
SELECT * INTO Table2
FROM [;database=C:\A.mdb].Table1;
Code:
SELECT * INTO Table2
FROM Table1 IN 'C:\A.mdb';
-
Apr 20th, 2017, 01:41 PM
#3
Thread Starter
Fanatic Member
Re: Copy Access table to another database
Thanks Dexwerx,
Can you please give me the correct syntax for:
from table costcentres in database "c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb.costcentres" into my opened database set as dBase - I presume like in:
Set rs = dBase.OpenRecordset("SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb.costcentres;]", dbOpenDynaset)
Thanks
-
Apr 20th, 2017, 01:44 PM
#4
Re: Copy Access table to another database
Originally Posted by Peekay
I wish to copy one table which exists in Access database A (structure and data) to Access database B using VB6 and DAO without doing it record for record.
Any help will be appreciated.
Thanks
If you want to do it with DAO objects:
Code:
' Copies a table from one database to another, it can copy only structure or also data.
' If the databases are already open, send them in the nDBOrig and nDBDest parameters, otherwise send their paths in nDBOrigPath and nDBDestPath
Public Sub CopyTableFromDBToDB(nTableName As String, nCopyData As Boolean, Optional nDBOrig As Database, Optional nDBDest As Database, Optional nDBOrigPath As String, Optional nDBDestPath As String)
Dim iDBOrig As Database
Dim iDBDest As Database
Dim iTableOrig As TableDef
Dim iTableDest As TableDef
Dim iFieldOrig As Field
Dim iFieldDest As Field
Dim iIndexOrig As Index
Dim iIndexDest As Index
Dim iRecOrig As Recordset
Dim iRecDest As Recordset
If Not nDBOrig Is Nothing Then
Set iDBOrig = nDBOrig
Else
If Dir(nDBOrigPath) = "" Then
Err.Raise 9001, App.Title & ".CopyTableFromDBToDB", "File not found for origin Database."
End If
Set iDBOrig = DBEngine.OpenDatabase(nDBOrigPath)
End If
If Not nDBDest Is Nothing Then
Set iDBDest = nDBDest
Else
If Dir(nDBDestPath) = "" Then
Err.Raise 9002, App.Title & ".CopyTableFromDBToDB", "File not found for destination Database."
End If
Set iDBDest = DBEngine.OpenDatabase(nDBDestPath)
End If
On Error Resume Next
Set iTableOrig = iDBOrig.TableDefs(nTableName)
On Error GoTo 0
If iTableOrig Is Nothing Then
Err.Raise 9003, App.Title & ".CopyTableFromDBToDB", "Table not found in origin database."
End If
Set iTableDest = iDBDest.CreateTableDef(iTableOrig.Name)
For Each iFieldOrig In iTableOrig.Fields
Set iFieldDest = iTableDest.CreateField(iFieldOrig.Name, iFieldOrig.Type)
If (iFieldOrig.Type And dbLong) = dbLong Then
If (iFieldOrig.Attributes And dbAutoIncrField) = dbAutoIncrField Then
iFieldDest.Attributes = iFieldDest.Attributes Or dbAutoIncrField
End If
End If
iFieldDest.Required = iFieldOrig.Required
If (iFieldOrig.Type And dbText) = dbText Then
iFieldDest.Size = iFieldOrig.Size
End If
If ((iFieldOrig.Type And dbText) = dbText) Or ((iFieldOrig.Type And dbMemo) = dbMemo) Then
iFieldDest.AllowZeroLength = iFieldOrig.AllowZeroLength
End If
If iFieldOrig.DefaultValue <> "" Then
iFieldDest.DefaultValue = iFieldOrig.DefaultValue
End If
iTableDest.Fields.Append iFieldDest
Next
For Each iIndexOrig In iTableOrig.Indexes
Set iIndexDest = iTableDest.CreateIndex(iIndexOrig.Name)
For Each iFieldOrig In iIndexOrig.Fields
iIndexDest.Fields.Append iIndexDest.CreateField(iFieldOrig.Name)
Next
iIndexDest.Unique = iIndexOrig.Unique
iIndexDest.Primary = iIndexOrig.Primary
iIndexDest.Required = iIndexOrig.Required
iTableDest.Indexes.Append iIndexDest
Next
iDBDest.TableDefs.Append iTableDest
If nCopyData Then
Set iRecOrig = iDBOrig.OpenRecordset(nTableName)
Set iRecDest = iDBDest.OpenRecordset(nTableName)
If Not iRecOrig.EOF Then
iRecOrig.MoveFirst
Do Until iRecOrig.EOF
iRecDest.AddNew
For Each iFieldOrig In iTableOrig.Fields
iRecDest.Fields(iFieldOrig.Name).Value = iRecOrig.Fields(iFieldOrig.Name).Value
If iFieldOrig.Required Then
If IsNull(iRecDest.Fields(iFieldOrig.Name).Value) Then
iRecDest.Fields(iFieldOrig.Name).Value = iFieldOrig.DefaultValue
End If
End If
Next
iRecDest.Update
iRecOrig.MoveNext
Loop
End If
End If
End Sub
-
Apr 20th, 2017, 01:59 PM
#5
Re: Copy Access table to another database
using this: https://support.microsoft.com/en-us/...uery-in-vb-3.0
More like:
Code:
dBase.Execute "SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb].costcentres"
-
Apr 20th, 2017, 02:11 PM
#6
Thread Starter
Fanatic Member
Re: Copy Access table to another database
Thank you both - I will try it.
-
Apr 21st, 2017, 01:55 AM
#7
Thread Starter
Fanatic Member
Re: Copy Access table to another database
DexWerx,
I am almost there. How do I handle a password in the database I read from?
Thanks
-
Apr 21st, 2017, 06:50 AM
#8
Re: Copy Access table to another database
Code:
SELECT * INTO costcentres FROM [;database=c:\dropbox\my programs\ibms 2017\gm ph 2017.mdb;PWD=mypwd].costcentres
-
Apr 21st, 2017, 07:59 AM
#9
Thread Starter
Fanatic Member
Re: Copy Access table to another database
DexWerx,
Thanks - I hope this is the last obtacle. It will not overwrite the table if it exists in the target database.
-
Apr 21st, 2017, 08:01 AM
#10
Thread Starter
Fanatic Member
Re: Copy Access table to another database
DexWerx,
Somehow it does not allow me to rate your post. Am I missing something?
-
Apr 21st, 2017, 08:22 AM
#11
Re: Copy Access table to another database
It will fail if the table exists in the other database. the INTO clause is used to create the table on the fly from a select statement. If the table exists then you use a standard insert statement....
Insert into tableName from XXXXX
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Apr 21st, 2017, 09:14 AM
#12
Re: Copy Access table to another database
Originally Posted by Peekay
DexWerx,
Somehow it does not allow me to rate your post. Am I missing something?
Show us your code.
-
Apr 21st, 2017, 04:30 PM
#13
Re: Copy Access table to another database
g---I think he wants to 'RATE YOUR POST', but can't....
OP, if that is correct, what does it say? Something about spreading around more ratings?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|