*.mdb files with same structures, Need combining program in VB or MS Access

[arthurps]

I have 6 files with the name admission.mdb in seperate folders, C:\Admin_1\admission.mdb, C:\Admin_2\admission.mdb, C:\Admin_3\admission.mdb, C:\Admin_4\admission.mdb, C:\Admin_5\admission.mdb, C:\Admin_6\admission.mdb.

I need to combinine them and store in C:\Admin folder has admission.mdb.

I need a VB program or MS Access code.

Please do the needful at the earliest.

Thanking you,
Arthur.

[Killer42]

Do you need to do this once, or set up something which can do it again later?

For a "quick and dirty" method, just create your new database, and link all the tables to it. Create a Union query to join the tables together into one big one. Then use that as input to an Append query to append to the new table.

For more info, I'd recommend hitting the Access forum - there's a lot more Access experience available there.

[arthurps]

Thank you for your excellent support. I appreciate your help.

I need to do this once a week i.e. on Saturday. I need *.exe file to be developed in VB and put it on the remote computer, which will be in rural part. Where phone connections or mobile networks are not available. The people who will be using have very less knowledge of computers. So I want to create a shortcut on the desktop for them to double-click.

[Killer42]

Ok. What sort of structure is there, to be copied? For instance, ins this a single table? Or a bunch of tables, queries, forms, reports and so on? Any relationships between the tables? Things like relationships will complicate things a bit, because you can't just dump all the records from each table into the new one without adhering to the relationships.

(I might ask one or two Access experts to take a look at this thread. If it turns out to be something they do all the time, I might be just wasting my time trying to work it out).

[NeoPa]

Can you explain the working process a little more clearly.
What triggers what?
What exactly do you need to happen on an ongoing basis?
Presumably the data is all you need transferring?

[arthurps]

There are 6 mdb files which are exactly same copy, each residing in different directory. The mdb has 15 tables and 3 queries,
no reports and no forms. The stucture is as follows.

PID - Number
name - Text
dob - Text
age - Number
Addr - Text
Addr1 - Text
Addr2 - Text
city - Text
pin - number
state - Text
phone - Text
Patient - Text
A1 - Number
A1a - Text
A1b - Text
A1c - Text
A1d - Text
A1e - Text
A1f - Text
A1g - Text
A1h - Text
A1i - Text
A2a - Text
A2b - Text
A2c - Text
A2d - Text
A2e - Text
A2f - Text
A2g - Text
A2h - Text
A2i - Text
A2j - Text
A2k - Text
A2l - Text
Adate - Date

First I need to copy first directories mdb file to a different location or directory and then start updating the rest of the mdb on it.

[hariharanmca]

can u give brief what are tables inside the .mdb file

[arthurps]

Sure,

The tables are

Class01
Class02
Class03
Class04
phc1
phc2
phc3
govchc1
govchc2
govchc3
govchc4

inside this tables, there are about 20 to 30 fields.

[NeoPa]

I would think you're looking at a Front End database (Could be your main one in C:\Admin or a separate one if you prefer) where all the tables in the other databases are linked in.
You didn't really answer my questions (I suspect you tried so I'm not having a go) so this will be necessarily vague, but you should get the idea and have the way forward mapped out at least.
The weekly job would have to :

1. Delete any historical records you no longer need from the main tables.
   This is assuming that is required. You didn't specify.
2. Run a UNION APPEND query to add any required records from your various sub-tables into the main tables.
3. Repeat steps 1 & 2 as required for each subsequent table that requires this treatment.

How you schedule this job is down to you. Only you know what you require in this respect.
Individual steps can be broken down further if required. Just let us know if there is anything you don't follow.

[arthurps]

Yes Sir,

You are correct, I need to delete everyting before it appeneds from all the current tables.

I just need a VB program where all the 6 access files will be updated to a 7th access file which will be in the different directory.

[NeoPa]

I'm sorry Arthur, but we don't provide ready made solutions for you. We assist with your development.
I have laid out (in post #9) the main steps you need to follow to produce the results you need. As stated in that post, I'm willing to help you over any complicated bits you struggle on. I'm not willing to provide you with a ready made solution, nor do all the leg-work for you.
Let me/us know where you get stuck, or what you find difficult to follow, and we will help you over those parts.

[arthurps]

Thank you so much for you support. I have gone through your steps mentioned in Post 9. I have created a append query in the new data base in which I need to combine the rest of the databases. First I delete the content of the table and then, I just append the fields table by table. I have created the reports in each database for the total number of records and compare the same with the combined database for confirmation.

I once again thank you for your time and effort.

I appriciate this support given by one and all in this forum.

You can close this Thread.

Thanking you,

Arthur.

[Killer42]

You can close this Thread.

Will do.

I'm glad to see you got it sorted. (Thanks for your input, NeoPa.)

It may seem harsh at times, but in the long run it's a lot more helpful for all of us if we can make this a "learning experience" rather than providing a cut-and-paste solution.

[NeoPa]

I'm glad to see you got it sorted. (Thanks for your input, NeoPa.)

No problem.
I was very pleased to hear Arthur had managed to get the process sorted. Nicely done.

As a bonus, I have a little VBA routine which clears records from tables (Saves coding each one).

Private Const conClearSQL As String = "DELETE * FROM [%T]%W"
 
'ClearTable clears the named table.
Public Sub ClearTable(strTable As String, _
                      Optional strWhere As String = "")
    Dim strSQL As String
 
    On Error GoTo CTError
    strTable = CurrentDb.TableDefs(strTable).Name
    On Error GoTo 0
 
    strSQL = Replace(conClearSQL, "%T", strTable)
    strSQL = Replace(strSQL, "%W", IIf(strWhere = "", "", " WHERE " & strWhere))
    Call DoCmd.RunSQL(strSQL, False)
    Exit Sub
 
CTError:
    Call MsgBox(Prompt:="Invalid table {" & strTable & "}", _
                Title:="ClearTable")
End Sub

[Killer42]

Nice one. Thanks for that, NeoPa.

One thing occurs to me on reading this. The debugging info seems to ignore the possibility of a bad WHERE clause.

(I will close the thread, once we finish discussing it.)

[NeoPa]

One thing occurs to me on reading this. The debugging info seems to ignore the possibility of a bad WHERE clause.

I don't follow. What debugging info (Probably missing something obvious here)?

(I will close the thread, once we finish discussing it.)

No need. It is not policy actually to close threads for the very reason of continued discussion. It is only important to know that the OP is satisfied with their answer, which is the case already in this thread :)

[Killer42]

CTError:
    Call MsgBox(Prompt:="Invalid table {" & strTable & "}", _
                Title:="ClearTable")

This debugging info. It shows the table that was specified, but not the WHERE. This is hardly the end of the world, but the info would be helpful in some circumstances. After all, if an SQL command fails it's not necessarily because of the table name.

[NeoPa]

Oh, I get what you mean now :)
Yes, I developed it originally as a simple clear down of the whole table. The WHERE clause bit was added afterwards. I suppose I didn't bother too much with the MsgBox code as I had never seen it triggered. You're welcome to make any amendments you feel are appropriate (Except, whatever you do, don't add the "=vb" to the CODE tags).

[NeoPa]

Just kidding. This is your area and I wouldn't presume to tell you how to choose your standards. Here's another version with the WHERE clause handled (basically).

Private Const conClearSQL As String = "DELETE * FROM [%T]%W"
 
'ClearTable clears the named table.
Public Sub ClearTable(strTable As String, _
                      Optional strWhere As String = "")
    Dim strSQL As String
 
    On Error GoTo CTError
    strTable = CurrentDb.TableDefs(strTable).Name
    On Error GoTo 0
 
    strSQL = Replace(conClearSQL, "%T", strTable)
    strSQL = Replace(strSQL, "%W", IIf(strWhere = "", "", " WHERE " & strWhere))
    Call DoCmd.RunSQL(strSQL, False)
    Exit Sub
 
CTError:
    strSQL = "Invalid : Table={" & strTable & _
             "} & WHERE={" & strWhere & "}"
    Call MsgBox(Prompt:=strSQL, Title:="ClearTable")
End Sub

I even said CODE=vb :(

[Killer42]

I even said CODE=vb :(

Thanks. I'd hate to have to ban you. :)

[arthurps]

Hey,

Thanks guys,

I had to modify little bit on my code. Has this pice of code was great to give error msg's. My code was not giving any errors, I was simply deleting the stuffs inside the table.

Once again thank you, I understand ur concern, Spoon feeding is not good, You need lean eating and mess it up, so you can learn from your mistakes.

Arthur.

[Killer42]

I had to modify little bit on my code. Has this pice of code was great to give error msg's. My code was not giving any errors, I was simply deleting the stuffs inside the table.

Yes, I think that was quite a nice bit of work by NeoPa. Glads to see we were able to help you out.

Once again thank you, I understand ur concern, Spoon feeding is not good, You need lean eating and mess it up, so you can learn from your mistakes.

That's so true. In alot of cases, we could provide code to copy and paste, which would overcome the immediate problem. But that developer would probably have to go looking for help again, next time they encounter something similar. It's better for everyone if the developer learns how to overcome the problem.

[NeoPa]

Nice one Arthur.
Good to see you're up and running with it :)