How to Communicate between more than 1 databases - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to Communicate between more than 1 databases

lucky_melucky_me Posts: 39Member
Hi there,
I need your help.Please...
Here is my problem....
There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
For example....
here is the network directory structure ....
\tempweekset1a.mdb
\tempweekset1a.mdb
\tempweekset1a.mdb
\tempweekset1a.mdb
.....................................
.....................................
and so on.

What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
Now I have to read each database table and write it into Mydb.mdb

Please hint me how can I do that.

a.mdb has 4 tables and My.mdb also have the same tables

Please please please Help me

Comments

  • sbecksteadsbeckstead Posts: 3Member
    : Hi there,
    : I need your help.Please...
    : Here is my problem....
    : There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
    : For example....
    : here is the network directory structure ....
    : \tempweekset1a.mdb
    : \tempweekset1a.mdb
    : \tempweekset1a.mdb
    : \tempweekset1a.mdb
    : .....................................
    : .....................................
    : and so on.
    :
    : What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
    : Now I have to read each database table and write it into Mydb.mdb
    :
    : Please hint me how can I do that.
    :
    : a.mdb has 4 tables and My.mdb also have the same tables
    :
    : Please please please Help me
    :
    :
    Sounds like you just need use 5 connection objects. Get the dataset from each database into something local, then write it to the master db.
    If you want details of how to do this try this book: Visual Basic .net Complete by sybex. Or if vb6 Visual Basic 6 complete by sybex. Has all you need to figure this out.
    Scott

  • dokken2dokken2 Posts: 532Member
    : : Hi there,
    : : I need your help.Please...
    : : Here is my problem....
    : : There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
    : : For example....
    : : here is the network directory structure ....
    : : \tempweekset1a.mdb
    : : \tempweekset1a.mdb
    : : \tempweekset1a.mdb
    : : \tempweekset1a.mdb
    : : .....................................
    : : .....................................
    : : and so on.
    : :
    : : What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
    : : Now I have to read each database table and write it into Mydb.mdb
    : :
    : : Please hint me how can I do that.
    : :
    : : a.mdb has 4 tables and My.mdb also have the same tables
    : :
    : : Please please please Help me
    : :
    : :
    : Sounds like you just need use 5 connection objects. Get the dataset from each database into something local, then write it to the master db.
    : If you want details of how to do this try this book: Visual Basic .net Complete by sybex. Or if vb6 Visual Basic 6 complete by sybex. Has all you need to figure this out.
    : Scott
    :
    :

    you can do this with a sql statement. loop through all the tables and db's on the network, updating the table and sourceMDB for each.


    [code]
    Sub SqlInsert()
    Dim Sql As String
    Dim SourceMDB As String

    'turn off system messages
    DoCmd.SetWarnings False

    'enclose filename in quotes
    SourceMDB = Chr$(34) & "e:acc2.mdb" & Chr$(34)

    'sql-insert
    'insert records from a table in another [SourceMdb] file
    Sql = "INSERT INTO table1 " & _
    "SELECT text1, addr1 " & _
    "FROM table1 IN " & SourceMDB & ";"
    DoCmd.RunSQL Sql

    'turn on system msgs
    DoCmd.SetWarnings True
    End Sub
    [/code]
  • HackmanCHackmanC Posts: 441Member
    Let's try something different ?

    Write a .txt file with the paths to 'all' the databases (An access database is a .MDW file), will assume the tables on all the databases has the same name and has the same structure.



    Open the .txt file and read line by line and open a connection to each database.

    [code]
    Dim conns() as ADODB.Connection

    open "list.txt" as #1
    x = 0
    redim conns(X)
    while not eof(1)
    readline 1, StringLine
    redim conns(x)
    conns(x) = new ADODB.Connection
    conns(x).ConnectionString = "MSACCESS, FILE=" & StringLine & "etc"
    conns(x).Open()
    x = x + 1
    wend
    close #1
    [/code]

    To do something with all connections...
    [code]
    x = 0
    redim conns(X)
    while x < Len(conns())
    dim e as ADODB.Command
    e = new ADODB.Command
    e.Connection = conns(x)
    e.CommandString = "INSERT INTO Tab (str1) VALUES ('" & value1 & "')"
    e.Execute()
    x = x + 1
    wend
    [/code]

    THIS IS AN ALGORITHM. Check the correct syntax and declare variables, etc ... Remember to close all connections on exit. You will need a big memory if you have 50 connections open at the same time.
    ---------------
    : : : Hi there,
    : : : I need your help.Please...
    : : : Here is my problem....
    : : : There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
    : : : For example....
    : : : here is the network directory structure ....
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : .....................................
    : : : .....................................
    : : : and so on.
    : : :
    : : : What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
    : : : Now I have to read each database table and write it into Mydb.mdb
    : : :
    : : : Please hint me how can I do that.
    : : :
    : : : a.mdb has 4 tables and My.mdb also have the same tables
    : : :
    : : : Please please please Help me
    : : :
    : : :
    : : Sounds like you just need use 5 connection objects. Get the dataset from each database into something local, then write it to the master db.
    : : If you want details of how to do this try this book: Visual Basic .net Complete by sybex. Or if vb6 Visual Basic 6 complete by sybex. Has all you need to figure this out.
    : : Scott
    : :
    : :
    :
    : you can do this with a sql statement. loop through all the tables and db's on the network, updating the table and sourceMDB for each.
    :
    :
    : [code]
    : Sub SqlInsert()
    : Dim Sql As String
    : Dim SourceMDB As String
    :
    : 'turn off system messages
    : DoCmd.SetWarnings False
    :
    : 'enclose filename in quotes
    : SourceMDB = Chr$(34) & "e:acc2.mdb" & Chr$(34)
    :
    : 'sql-insert
    : 'insert records from a table in another [SourceMdb] file
    : Sql = "INSERT INTO table1 " & _
    : "SELECT text1, addr1 " & _
    : "FROM table1 IN " & SourceMDB & ";"
    : DoCmd.RunSQL Sql
    :
    : 'turn on system msgs
    : DoCmd.SetWarnings True
    : End Sub
    : [/code]
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]

  • lucky_melucky_me Posts: 39Member
    : : : Hi there,
    : : : I need your help.Please...
    : : : Here is my problem....
    : : : There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
    : : : For example....
    : : : here is the network directory structure ....
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : \tempweekset1a.mdb
    : : : .....................................
    : : : .....................................
    : : : and so on.
    : : :
    : : : What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
    : : : Now I have to read each database table and write it into Mydb.mdb
    : : :
    : : : Please hint me how can I do that.
    : : :
    : : : a.mdb has 4 tables and My.mdb also have the same tables
    : : :
    : : : Please please please Help me
    : : :
    : : :
    : : Sounds like you just need use 5 connection objects. Get the dataset from each database into something local, then write it to the master db.
    : : If you want details of how to do this try this book: Visual Basic .net Complete by sybex. Or if vb6 Visual Basic 6 complete by sybex. Has all you need to figure this out.
    : : Scott
    : :
    : :
    :
    : you can do this with a sql statement. loop through all the tables and db's on the network, updating the table and sourceMDB for each.
    :
    :
    : [code]
    : Sub SqlInsert()
    : Dim Sql As String
    : Dim SourceMDB As String
    :
    : 'turn off system messages
    : DoCmd.SetWarnings False
    :
    : 'enclose filename in quotes
    : SourceMDB = Chr$(34) & "e:acc2.mdb" & Chr$(34)
    :
    : 'sql-insert
    : 'insert records from a table in another [SourceMdb] file
    : Sql = "INSERT INTO table1 " & _
    : "SELECT text1, addr1 " & _
    : "FROM table1 IN " & SourceMDB & ";"
    : DoCmd.RunSQL Sql
    :
    : 'turn on system msgs
    : DoCmd.SetWarnings True
    : End Sub
    : [/code]
    :
    Thank you so much. It seems very good approach.But it needs the file path.In my case there are more than 796 databases.I need to find all the paths and then proceed.Still I need more than 2 connections.My question is can I switch connections in a block.I want to use global connections.
    Thanks for
  • lucky_melucky_me Posts: 39Member
    Thanks Hackman,
    I also tried the same way.I found all the paths using DOS command and inserted it into a textfile.Imported the text file into an access table and then applied the loop.It worked fine when I was using VB Script.I want to create kind of VB application so I am not sure how to switch between connections because it'll need more than 2 connections.
    And I was not sure about switching connections.Thanks to let me know that I can do it using multiple connections.Please let me know what'd be the syntax.

    and one more thing Can I use the DOS script/command from VB to find the path and insert it into a text file.

    I tried to find out a good book but not getting any :(

    Thanks laot for your help.
    Regards
    Lucky

    : Let's try something different ?
    :
    : Write a .txt file with the paths to 'all' the databases (An access database is a .MDW file), will assume the tables on all the databases has the same name and has the same structure.
    :
    :
    :
    : Open the .txt file and read line by line and open a connection to each database.
    :
    : [code]
    : Dim conns() as ADODB.Connection
    :
    : open "list.txt" as #1
    : x = 0
    : redim conns(X)
    : while not eof(1)
    : readline 1, StringLine
    : redim conns(x)
    : conns(x) = new ADODB.Connection
    : conns(x).ConnectionString = "MSACCESS, FILE=" & StringLine & "etc"
    : conns(x).Open()
    : x = x + 1
    : wend
    : close #1
    : [/code]
    :
    : To do something with all connections...
    : [code]
    : x = 0
    : redim conns(X)
    : while x < Len(conns())
    : dim e as ADODB.Command
    : e = new ADODB.Command
    : e.Connection = conns(x)
    : e.CommandString = "INSERT INTO Tab (str1) VALUES ('" & value1 & "')"
    : e.Execute()
    : x = x + 1
    : wend
    : [/code]
    :
    : THIS IS AN ALGORITHM. Check the correct syntax and declare variables, etc ... Remember to close all connections on exit. You will need a big memory if you have 50 connections open at the same time.
    : ---------------
    : : : : Hi there,
    : : : : I need your help.Please...
    : : : : Here is my problem....
    : : : : There are more than 50 access databases on my Network each has 4 tables.I have to find out all the databases and fetch all the data individually from each database and each tables, and populate a master database (in individual table).
    : : : : For example....
    : : : : here is the network directory structure ....
    : : : : \tempweekset1a.mdb
    : : : : \tempweekset1a.mdb
    : : : : \tempweekset1a.mdb
    : : : : \tempweekset1a.mdb
    : : : : .....................................
    : : : : .....................................
    : : : : and so on.
    : : : :
    : : : : What I did.... I found all the paths of the databases and stored the path into a temporary table called as Temp (c:mydb.mdb)in other database.
    : : : : Now I have to read each database table and write it into Mydb.mdb
    : : : :
    : : : : Please hint me how can I do that.
    : : : :
    : : : : a.mdb has 4 tables and My.mdb also have the same tables
    : : : :
    : : : : Please please please Help me
    : : : :
    : : : :
    : : : Sounds like you just need use 5 connection objects. Get the dataset from each database into something local, then write it to the master db.
    : : : If you want details of how to do this try this book: Visual Basic .net Complete by sybex. Or if vb6 Visual Basic 6 complete by sybex. Has all you need to figure this out.
    : : : Scott
    : : :
    : : :
    : :
    : : you can do this with a sql statement. loop through all the tables and db's on the network, updating the table and sourceMDB for each.
    : :
    : :
    : : [code]
    : : Sub SqlInsert()
    : : Dim Sql As String
    : : Dim SourceMDB As String
    : :
    : : 'turn off system messages
    : : DoCmd.SetWarnings False
    : :
    : : 'enclose filename in quotes
    : : SourceMDB = Chr$(34) & "e:acc2.mdb" & Chr$(34)
    : :
    : : 'sql-insert
    : : 'insert records from a table in another [SourceMdb] file
    : : Sql = "INSERT INTO table1 " & _
    : : "SELECT text1, addr1 " & _
    : : "FROM table1 IN " & SourceMDB & ";"
    : : DoCmd.RunSQL Sql
    : :
    : : 'turn on system msgs
    : : DoCmd.SetWarnings True
    : : End Sub
    : : [/code]
    : :
    :
    : [red]Good luck![/red]
    : [blue]Hackman[/blue]
    :
    :

Sign In or Register to comment.