Solved

import data from another MDB file

Posted on 2003-11-08
6
317 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro which automatically sends attachment to Outlook 14 78
Validating VB6 Function 19 65
VB6 - Convert HH:MM into Decimal 8 62
Dinamic report to Crosstab query 9 33
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…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

828 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