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
: 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
: : 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]
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]
: : : 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
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]
:
: