[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

import data from another MDB file

Posted on 2003-11-08
6
Medium Priority
?
326 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 600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Suggested Courses

873 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