Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

import data from another MDB file

Posted on 2003-11-08
6
Medium Priority
?
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

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…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

609 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