Merging of folders data into another mdb file

[veer]

the programe is about i have one folder named newdata which contain 46 folders and each folder containg multiple mdb files and with in mdb files there are two tables.
i want to merge all these folders data into one mdb file
my code is

Set dbparent = Workspaces(0).O penDatabase(txt parentMDBFile)
sDirFolder = Dir(txtchildMDB File.Text & "\", vbDirectory)
Do While sDirFolder <> ""
ReDim Preserve sArrayFolder(ic ount) As String
sArrayFolder(ic ount) = sDirFolder
icount = icount + 1
lblcount = icount
sDirFolder = Dir()
Loop
For icount = 2 To UBound(sArrayFo lder())
sDirFolder = Dir(txtchildMDB File.Text & "\" & sArrayFolder(ic ount) & "\*.mdb")
Do While sDirFolder <> ""
Set dbparent = Workspaces(0).O penDatabase(sDi rFolder)
squery = " insert inoto " & txtparenttable & " IN '" & txtparentMDBFil e & " ' select * from " & txtchildMDBFile & "\" & sDirFolder & " "
dbparent.Execut e (squery)
Loop
MsgBox "movenext"
Next
" the error is on do while loop i,e
do while sdirfolder<> ""
............... .............
............... ....
loop
and errro is couldnot open the .mdb file please help me

[QVeen72]

Hi,

Change the Code to :
Keeping a Breakpoint and checking the Variables will help you..
Remove the Star
[code=vb]
sDirFolder = txtchildMDBFile .Text & "\" & sArrayFolder(ic ount) & "\MyDB.mdb" )
Do While Dir(sDirFolder, vbDirectory) <> ""
Set dbparent = Workspaces(0).O penDatabase(sDi rFolder)

[/code]

Change MyDB.mdb, to ur db name

Regards
Veena

[veer]

if i remove the star and put the database name then it is not entering into the do while loop and not inserting any value in txtparentmdbfil e.text please send me other option

thanks for help in advance

Hi,

Change the Code to :
Keeping a Breakpoint and checking the Variables will help you..
Remove the Star
[code=vb]
sDirFolder = txtchildMDBFile .Text & "\" & sArrayFolder(ic ount) & "\MyDB.mdb" )
Do While Dir(sDirFolder, vbDirectory) <> ""
Set dbparent = Workspaces(0).O penDatabase(sDi rFolder)

[/code]

Change MyDB.mdb, to ur db name

Regards
Veena

[QVeen72]

Hi,

Dont Only remove the Star.. Change the Code as I have said in My previous Post.. Read it carefully..

Regards
Veena

[veer]

Helo,
sorry, its not working . In place of star how i could give the name of database because i have not any single database
you mentioned the breaking point , i am not understanding it

if i change the other code then it shows the drive or disk path error
please send me other code so that i can merge the data.
will you please send the formatted code of my code.

Hi,

Dont Only remove the Star.. Change the Code as I have said in My previous Post.. Read it carefully..

Regards
Veena

[QVeen72]

Hi,

How is the Folder tree..?
I'am not able to visualise the Folder's and its Mdb's.. can you explain again.. and what is there in txtparent.. and txtchild...?

Regards
Veena

[veer]

folder Tree:
MainFolder -> 46 folders -> Every Folder contain more than 50 mdb files ->
Every Mdb File has two tables, i want to merge all tables data of each mdb file into txtparenttable.
This folder tree is not on my hard drive
The txtparentmdbfil e is the path for the table where i want to merge the data and txtchildmdbfile is the path from where i am accessing the data i,e 48 folders
The error is on line
set dbparent = workspaces(0).o pendatabase(sdi rfolder)
error is " Could not find Ist.mdb file" where Ist is any first mdb file in first folder

Dim dbmaster As DAO.Database
Dim dbparent As DAO.Database
Dim rsmaster As DAO.Recordset
Dim sDirFolder As String
Dim sArrayFolder() As String
Dim squery As String
Dim icount As Integer
Dim sTable As String

Set dbparent = Workspaces(0).O penDatabase(txt parentMDBFile)
sDirFolder = Dir(txtchildMDB File.Text & "\", vbDirectory)
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then
ReDim Preserve sArrayFolder(ic ount) As String
sArrayFolder(ic ount) = sDirFolder
icount = icount + 1
lblmerge.Captio n = "Directorie s Found="
lblcount = icount
End If
sDirFolder = Dir()
Loop
For icount = 0 To UBound(sArrayFo lder())
sDirFolder = Dir(txtchildMDB File.Text & "\" & sArrayFolder(ic ount) & "\*.mdb", vbDirectory)
Do While sDirFolder <> ""
If sDirFolder <> "." And sDirFolder <> ".." Then

sTable = Right(sDirFolde r, InStrRev(sDirFo lder, "\"))
sTable = Replace(sTable, ".mdb", "")
sTable = "E" & sTable
Set dbparent = Workspaces(0).O penDatabase(sDi rFolder)
End If
squery = " insert into " & txtparenttable & " IN '" & txtparentMDBFil e & " ' select * from " & sTable
dbparent.Execut e (squery)
Loop

Next

Hi,

How is the Folder tree..?
I'am not able to visualise the Folder's and its Mdb's.. can you explain again.. and what is there in txtparent.. and txtchild...?

Regards
Veena