There are two databases. the working database has a single table which holds all of the Information i require to pass into a MIS system(so this has a fixed stucture).
The Data Database contains the files the users can access and update data as and when reuired. This is a pharmacy database so holds information such as drugs used by department how many are used etc. This data is updated regularly and users are happy with the structure in place.
The first database with the table used to be contained within the data database and i used append query's to rebuild this information every time.
This was working perfectly until yesterday when my poor computer gave up and i was throughn an out of memory message and the only way i could get things to workwas to split out my working table into a new database.
So now i have to get a way of updating the working database remotley from the second database.
Im not to confident with Access as most of my work is with Excel.
My Idea was to use VBA to Access the data Databse combine create a temporary table within the working database then running some SQL to add any new records to my Working Table.
Hope this clears thing up a bit
Any Help will be greatly appreciated
Dim dbThisDB As Database
Dim wrkGetData As Workspace
Dim dbDataDB As Database
Dim MySQL As String
Dim TableArray() As String, tablename
Dim I As Integer
Set dbThisDB = CurrentDb()
'clear out the Drill Table
DoCmd.RunSQL "DELETE DISTINCTROW [Pharmacy_Drill].* " & _
'set up a connection to the 2nd Database
Set wrkGetData = CreateWorkspace("", "Admin", "", dbUseJet)
Set dbDataDB = wrkGetData.OpenDatabase("S:\Apps\MIS\DATABASE\Pharmacy_Data.mdb")
'Fill an array with the tablenames of data database
ReDim TableArray(dbDataDB.TableDefs.Count - 1)
For I = 1 To .TableDefs.Count - 1
TableArray(I) = .TableDefs(I).Name
'Impot the Table
For Each tablename In TableArray
If tablename Like "*Actuals*" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", "Pharmacy_Data.mdb", _
acTable, tablename, "TempTable"
'Insert Table into Pharmacy_Drill Table
MySQL = "INSERT INTO [Pharmacy_Drill]& _
"SELECT DISTINCTROW [TempTable].* FROM [TempTable]"
'delete the tempTable
DoCmd.DeleteObject acTable, "TempTable"
' clear the object variables
Set dbThisDB = Nothing
Set wrkGetData = Nothing
Set dbDataDB = Nothing
Im Still working on it but this will be the bones of my code.
If anyone can think of a better or faster way please let me know
David, I'm interested in your problem because I need to do something very similar and being new to advanced Access techniques and VBA I don't know how to accomplish it. I also need to have a separate .mdb file update when I update my working .mdb file. Others have suggested a series of update/append queries but I don't know how to go about setting them up to make this work. I tried to follow your new solution but I can't keep up. Could you explain briefly how your append queries worked? Thanks!
the DELETE DISTINCTROW query that i used deletes all the records from my (Working) table
To Get the Workspace i used the Access Jey Engine as i have no access rights to use ODBC on my Networks.
Opened the Data Databse and filled an array with all of the table names
Now all the tables that needed to be imported were called something_Actuals_Something so for each name in my array i searched for the work actuals
I Imported each table that matched the criteria then ran an update query
INSERT INTO (WorkingTable) SELECT DISTINCTROW (Everything from Temp Table) From (TempTable)
This appends all unique records from the Temp tables to the Working Table
I then deleted the temp table and moved on to the Next.
If you can Link your tables into your database then that would be a better way to do this as you can just use the Standard queries, I couldn't as i kept on getting errors, something like out of memory or something similar(It's a pretty big DB)
If you need anything else just post again and ill try to Help.
A god idea to learn a bit of SQL Generate a few Queries in Access and choose the SQL View(well thats what i did) then start ttrying to write some yourself
I got the linked tables in now. unfortunatley i can't actual do anything to my database with them anymore, The Network is so short of memory that no additional data can go into the system. So when i was making a back up of the data Tables the whole network went out of memory.
The code i wrote worked fine as i was only testing and the delete query saved me a lot of space (approx 1 million rows of data initally) so it was fine to fill it up to a certain point, My queries added data to this table and was overloading the hard disk space that was left.
what i was thinking at the time of writing the code was that the database couldnt be any bigger, were talking about probably 3 million rows at the time so i created a new database to try and ease some of the burden.
When i linked before and ran the queries it failed on me.
Thanks for everyones help. the next job will be to see what can be cleared off of this network drive.
As we are not the Data Controllers for the data within the DB We cannot store it in Locations where the users don't have access to it, All on the same network drive, the working table's Location is fixed as it is a drill down for our MIS System.
So hence neither database can move and there is no space to add more data to both DB's
Hope that makes some sort of sense.
Ive only been learning how to do this since Tuesday, The only thing i did before was write simple queries as everything was all pre setup but the guy who set it up has disapeared. All i can say is Arghh