Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: Transfer data from one Database to Another

    Hi

    This is Probably pretty simple.

    I am using Access 97.

    What im trying to do is to create some VBA that will combine tables of information from a data Database into a Working Database.

    Im struggling to figure out what to do

    My Idea was to create a connection to the Data Database (I can do this).
    Create recordsets of the data i want to transfer (can do this).

    Then insert this Information into the table (not sure how to do this)

    This used to get done from the same database by queries but now there is to much data and it crashes the DB (probably the Machine im using)

    Any help would be greatly recieved

    David

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am not sure I understand exactly. The data database, is it all your data? And is the working database all your forms and report?

    Or, is the data database an overflow for the working database. Example, you have TableA in the working database with 100,000 records and TableA in the data database with 200,000 records.

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ill try to explain things a bit better.

    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
    David

  4. #4
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    To update the 'data' db you have to be able to gain access to it from your PC. From your 2nd (working) db try 'linking' the table that you want to be able to update and see if you can open it up.

    To be honest I can't see you being able to do as if you can't get to it from one db it seems logical that you won't be able to get to it thru another.

    Lemme know tho and we'll take it from there

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Got an answer to my Question.

    This is how i am approaching it now.

    Code:
    'Current Database
        Dim dbThisDB As Database
        
        'Data Database
        Dim wrkGetData As Workspace
        Dim dbDataDB As Database
        
        'Other Variables
        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].* " & _
                        "FROM [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)
        
        With dbDataDB
            For I = 1 To .TableDefs.Count - 1
                TableArray(I) = .TableDefs(I).Name
            Next I
        End With
        
        'Impot the Table
        For Each tablename In TableArray
            'Import Table
            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]"
                DoCmd.RunSQL MySQL
                'delete the tempTable
                DoCmd.DeleteObject acTable, "TempTable"
            End If
        Next tablename
        
        ' 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

    Thanks

    David

  6. #6
    Join Date
    Jun 2004
    Location
    Moab, UT
    Posts
    10
    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!

    Heather

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Heather,

    Ill Try my best

    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
    i.e.

    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

    Good Luck
    David

  8. #8
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Unless I've completely got the wrong of the stick, all you need to do is link the table (using linked table manager). You don't need any code at all

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    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.

    David

  10. #10
    Join Date
    Apr 2003
    Posts
    280
    Why not just use the Append queries to transfer that data to the other database?

  11. #11
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Both DB's are on the Same Network

    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

    David

  12. #12
    Join Date
    Apr 2003
    Posts
    280
    Archive the data and delete the old ones..

  13. #13
    Join Date
    Jun 2004
    Location
    Moab, UT
    Posts
    10
    Thanks, David. I'll take a closer look at this and see what I might be able to use.
    Heather

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •