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.
:
: <!-- FILE list.txt
: \\SERVER1\path1\file1.mdw
: \\SERVER2\path1\fileX.mdw
: -->
:
: Open the .txt file and read line by line and open a connection to each database.
:
:
: 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
:
:
: To do something with all connections...
:
: 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
:
:
: 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 ....
: : : : \\temp\week\set1\<directory1>\a.mdb
: : : : \\temp\week\set1\<directory2>\a.mdb
: : : : \\temp\week\set1\<directory3>\a.mdb
: : : : \\temp\week\set1\<directory4>\a.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.
: :
: :
: :
: : 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
: :
: :
:
:
Good luck!
:
Hackman
:
:
