Solved

import data from another MDB file

Posted on 2003-11-08
6
315 Views
Last Modified: 2010-05-18
Hi,

a) I want to import records from another access MDB file. Both the MDB files have same table, fields, etc.
e.g.
file1.mdb has table1 with name, address which are memo fields.
file2.mdb has table1 with name, address which are memo fields.
import all from file2 --> file1.mdb

b) I want to do that in DAO as it works in VB5(4 & 3 & 6)
c) I shall appreciate help if instead of using DAO object on the form, connection string is used.
d) extra 50 points for password protected MDB file.

Thank you in advance for your help.
0
Comment
Question by:aftab2003
6 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 150 total points
ID: 9709427
   Dim DB1 As Database
    Dim DB2 As Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset

    Set DB1 = OpenDatabase("C:\myfolder\database1.mdb", , True, "UID=UserName;PWD=password;")
    Set DB2 = OpenDatabase("C:\myfolder\database2.mdb", , False, "UID=UserName;PWD=password;")
    Set rs1 = DB1.OpenRecordset("Table1")
    Set rs2 = DB2.OpenRecordset("Table1")
    rs1.MoveLast
    If rs1.RecordCount > 0 Then
        rs1.MoveFirst
        Do Until rs1.EOF
            rs2.AddNew
            rs2.Fields("name").Value = rs1.Fields("name").Value
            '...
            rs2.Update
            rs1.MoveNext
        Loop
    End If
    rs2.Close
    rs1.Close
    DB2.Close
    DB1.Close

0
 
LVL 6

Expert Comment

by:VK
ID: 9715757
Hello aftab2003!

What do you mean exactly?

1. Do you want to perform your desired action with all tables or just with table1?
2. Do you want to replace the whole content of the tables or do you want to merge the tables?

Assuming you want to merge two tables located at different databses via DAO.

Open the Database which should be updated (db).
Then execute the insert command with the correct path and filename of the source database:

db.ececute(INSERT INTO Table1 IN 'C:\Test.mdb' SELECT * FROM Table1")

Warnings:

When you try to append a record to the destination table you could get an error.
The error could be caused by(examples):

1. Duplicate Keys
2. Invalid References
3. ...

V.K.


0
 

Expert Comment

by:anoop_chekkattu
ID: 9716801
I think both ways are correct

in GrahamSkans code give a privision to check for duplicate values inside
    'Do Until rs1.EOF' loop

or if u want a copy of the database file, better use 'CopyFile' command in the 'WIN32API' or use 'Shell' command
           
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:aftab2003
ID: 9717793
Hi,

a) GrahamSkan's code worked in VB6.
Full credit to him.
b) And VK: you just did the thing with just
one line of code - wonderful.

You people are masters.
0
 

Author Comment

by:aftab2003
ID: 9717826
Hi,

Here is some code that works in old old visual basic where password cannot be enabled(Access1.1 I guess !):
[Following GraHamSkan's code]

  Dim DB1 As Database
  Dim DB2 As Database

  Dim rs1 As table
  Dim rs2 As table
 
  'Dim rs1 As DAO.Recordset (Not in vb oldies)
  'Dim rs2 As DAO.Recordset

  Set DB1 = OpenDatabase("C:\db1.mdb", True, True) ', "UID=UserName;PWD=password;")
  Set DB2 = OpenDatabase("C:\db2.mdb", True, False) ', "UID=UserName;PWD=password;")
  Set rs1 = DB1.OpenTable("DigiCard")            'OpenRecordset("Table1")
  Set rs2 = DB2.OpenTable("DigiCard")            'OpenRecordset("Table1")
  rs2.MoveLast
  If rs2.RecordCount > 0 Then
    MsgBox (rs1.RecordCount), , "DB1"
    MsgBox (rs2.RecordCount), , "DB2"
    rs2.MoveFirst
    Do Until rs1.EOF
      rs2.AddNew
      rs2.Fields("Question").Value = rs1.Fields("Question").Value
      '...
      rs2.Update
      rs1.MoveNext
    Loop
  End If
  rs2.Close
  rs1.Close
  DB2.Close
  DB1.Close

Thanks for your help.
Will wrap it up in a week.
0
 

Author Comment

by:aftab2003
ID: 9772449
Dear Graham Skan,

100 + 50
Thank you for your code - it was great help.
thanks to VK & Anup
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

16 Experts available now in Live!

Get 1:1 Help Now