Solved

import data from another MDB file

Posted on 2003-11-08
6
314 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 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

15 Experts available now in Live!

Get 1:1 Help Now