Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47

    Smile Unanswered: Append Query? Mirroring Prod - Linked Tables

    Use Case: I am mirroring a SharePoint Site / db with MS Access. The main table lets call it "Packages" has not a great deal of records with respect to the database world, but each and everyone is a "source of record".

    We have had several records "get deleted" out of SharePoint, and I mistakingly believed that I could take an Archived Copy of the Access DB, launch it and retrieve the "deleted record from last week's back up. (Yep the tables are linked and they auto sync to the current dataset on SharePoint when you launch Access. Long and short as you know, Access only at this point stores "pointers" or "Hooks" into the SharePoint table and displays them.

    Long and short is this:

    I have created a NON LINKED TABLE using a make table query copying records from the source "Packages" table. I opened the table and today's records are there. I understand that it is a "snapshot in time".

    I want to create a query that will update only NEW RECORDS from the orignal source "Packages" and append them to the COPY of PACKAGES. I have created an APPEND query, took a peek from design view and sure enough, there were the original 800 records. Not the desired result. I did NOT exectute the query. I saved it, knowing I have to add some more criteria. Hence this post.

    End State: Create a run Macro or VBScript. Select Query Append NEW RECORDS from Source table (the one linked to SharePoint) and Copy them to the CopyTable of Packages. End (On further thought, I might need to compare records from one table Source and Destination so I don't get duplicate records)

    I intend to peform back ups for the Access DB as I do now and store the back ups of the Access DB on the Share Drive. My supposition is thta I will have the snap shot for that day and can find a deleted record in the non linked table.

    Best recommendations please. Resetting user permissions to not allow deletes unless you are in the "owners group" is the obvious security choice, but not possible under my circumstance.

    Thanks in advance!!
    Fighting Terror One Query @ a Time

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I first hope that the SQL Server behind Sharepoint had a valid backup strategy.

    Appending new rows from a table (source) into another (destination) is not very complicated provided that the source table has a primary key (or an Identity column). In the following example, [dbo_Tbl_Clients] (source) is a linked table (data are stored in a database on a SQL Server), while [Tbl_Clients] (Destination) is a local table. [SysCounter] is the primary key in both tables which have a similar structure with one exception: [SysCounter] is "seen" by Access as an Autonumber column in the table [dbo_Tbl_Clients] while it is defined as a Long Integer in the local table [Tbl_Clients]. In both tables, [SysCounter] has a primary key constraint. Here's a possible query to add new lines :
    Code:
    INSERT INTO Tbl_Clients ( SysCounter, 
                              CreationDate, 
                              Cust_Code, 
                              Cust_Group, 
                              Cust_Name, 
                              CountryCode, 
                              CityCode, 
                              PlantCode, 
                              DispatchCode, 
                              Inactive )
    SELECT SysCounter, 
           CreationDate, 
           Cust_Code, 
           Cust_Group, 
           Cust_Name, 
           CountryCode, 
           CityCode, 
           PlantCode, 
           DispatchCode, 
           Inactive
    FROM   dbo_Tbl_Clients
    WHERE  SysCounter IN ( SELECT dbo_Tbl_Clients.SysCounter
                           FROM   dbo_Tbl_Clients LEFT JOIN 
                                  Tbl_Clients ON dbo_Tbl_Clients.SysCounter = Tbl_Clients.SysCounter
                            WHERE Tbl_Clients.SysCounter Is Null
                         );
    If this is intended to be used in a backup strategy, I would not use this technique, though. You wrote "the original 800 records", which is not a lot. In such circumstances I would use a SELECT INTO query and copy the whole table every day into an Access backup database, the name of which could be derived form the current month and year and the name of the destination table being partly derived from the current date, something like this:
    Code:
    Sub Backup_Tbl_Clients()
    
        Const c_DbName As String = "BackUp_@M.mdb"
        Const c_SQL As String = "SELECT SysCounter, CreationDate, Cust_Code, Cust_Group, Cust_Name, " & _
                                       "CountryCode, CityCode, PlantCode, " & _
                                       "DispatchCode, Inactive " & _
                                "INTO @T IN @D " & _
                                "FROM dbo_Tbl_Clients;"
        Dim strDbName As String
        Dim strTableName As String
        
        strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
        strTableName = "Tbl_Clients_" & Format(Now, "mm_dd_hh_nn")
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
        CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sindho,

    Access 2007, drawing a data from a linked table to SharePoint.

    I believe I have it almost formatted correctly based on your 2nd example, which is what I think I want. I am catching an error on this line:
    Code:
    CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    One other question in the first line of this string segement, am I correct to assume where I have put in "Tbl_JARBBACKUP_ the VB Code will create a table in the new database called Tbl_JARBBACKUP with the year and month appended in the name ?
    Code:
    strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
        strTableName = "Tbl_JARBDBBACKUP_" & Format(Now, "mm_dd_hh_nn")
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
    Thanks in advance.
    Last edited by jstpierre; 02-22-12 at 10:44.
    Fighting Terror One Query @ a Time

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jstpierre View Post
    I am catching an error on this line:
    Code:
    CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    Could you post the error message and code? Did you also correctly change the constant c_SQL according the the structure of the table you want to backup? One can easily mistype something there.

    Quote Originally Posted by jstpierre View Post
    One other question in the first line of this string segement, am I correct to assume where I have put in "Tbl_JARBBACKUP_ the VB Code will create a table in the new database called Tbl_JARBBACKUP with the year and month appended in the name ?
    Code:
    strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
        strTableName = "Tbl_JARBDBBACKUP_" & Format(Now, "mm_dd_hh_nn")
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
    You're correct this is equivalent to:
    Code:
    strDbName = "BackUp_" & Format(Date, "yyyy_mm") & ".mdb"
    If the resulting file name is not found, a new database file is created by the CreateDatabase instruction. The same principle is used for the name of the table, using the month, day, hour and minute to assemble the trailing part of that name.
    Quote Originally Posted by jstpierre View Post
    Thanks in advance.
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sinndo,

    I opened the main table in Access 2007 in design view and then typed in the following fields in the select statement. You will certainly notice that there are XXX in fields and certain areas, this is for confidentiality. Here is the code as requested.

    Error produced is Syntax error in FROM clause: Could it be that I need to set the path to the db in the from clause?

    Code:
    Option Compare Database
    
    Sub Backup_Tbl_JXXX_Main_Data_Page()
    
    
    
        Const c_DbName As String = "BackUp_@M.mdb"
        Const c_SQL As String = "SELECT ID, [XXXX or OPEN PR Number], [POC's Name], [POC Phone#],[Date Received], [Requesting Org], [Requirement Type], " & _
                                       "[Cost Estimate], Description, Comments,[Date to XXXX], Status, [XXX OFFICE],[XXXX XXXX Date], [XXXX Number], XXX, [Deliver date or POP Start Date], XXX, [Requestor's Email Address],  " & _
                                       "[POP END Date], [XXX Certs], SDN, [Date to RM], [Date to COS], [Date to Legal], [Date to XXX], [Date to XXX], [XXX Name], [XXX Unit], [XXX Phone], [XXX Rotation Date], XXX6, XXX, XXX, XXX, XXX, [XXX Email], " & _
                                       "[XXX Vote], [XXX Vote], [XXX Vote], [XXX Vote],[XXX Vote], [XXX Vote], [XXX Comments], [XXX Comments], [XXX Comments], [XXX Comments], [C-8 Comments], [XXX XXX Comments], [XXX XXX CERT], Option, [Option Period Begin], " & _
                                       "[Option Period End], E-XXX, [XXX Vote], [XXX Comments], [Reason for Action], [Facts or Discussion], [Recommendation], [XXX Comments], [E-XXX URL], [XXX Advisory], [Short Title of Request] " & _
                                "INTO @T IN @D " & _
                                "FROM dbo_[XXXXJimsWB];"
        Dim strDbName As String
        Dim strTableName As String
        
        strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
        strTableName = "Tbl_XXXDBBACKUP_" & Format(Now, "mm_dd_hh_nn")
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
        CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    
    End Sub
    Understand that I am trying set this up in a test environment before writing a module on the Production DB.

    Thanks in advance.
    Fighting Terror One Query @ a Time

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jstpierre View Post
    Error produced is Syntax error in FROM clause: Could it be that I need to set the path to the db in the from clause?
    The query is supposed to be executed in the source database (i.e. the database containing the linked (attached) tables), so the FROM clause only is the name of the source table. On the contrary, strDbName which represents the destination database (i.e. the database that contains the backup tables), must contain the full path to that database if it's not located in Access default folder:
    Code:
    Const c_DbName As String = "C:\Documents and Settings\Sinndho\My documents\Access\BackUp_@M.mdb"
    strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
    Note: You can define or change the default folder. In Access 2007, click on the Office button (top-left) and select "Access Options". You can also determine what's this default folder by using the CurDir function: Open the VBA Editor (Alt+F11), then open the Immediate window (Ctrl+G) and type:
    Code:
    Print CurDir
    followed by the Enter key.
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Now I am getting an error, Run Time 3067 Query input must contain at leat one table or query.

    I have changed the first line:
    Code:
    Const c_DbName As String = "D:\Documents and Settings\My.Name\My Documents\BackUp_@m.mdb"
    In the FROM claue I have
    Code:
    "From dbo_[XXXX Main Data Page] ;"
    (My table name sadly has spaces in it as it is an alias presented by SharePoint so that is why I bracketed it [xxx].)

    I feel like a dunce...I know I am clearly missing the obvious
    Also the debugger is still highlighting the last string of code currentDB.Execute......

    Sorry for the headache....
    Last edited by jstpierre; 02-23-12 at 06:17.
    Fighting Terror One Query @ a Time

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Use an underscore, not a hyphen in the database name:
    Code:
    Const c_DbName As String = "D:\Documents and Settings\My.Name\My Documents\BackUp_@m.mdb"
    instead of:
    Code:
    Const c_DbName As String = "D:\Documents and Settings\My.Name\My Documents\BackUp-@m.mdb"
    2. The whole name of the table must be within the square brackets:
    Code:
    "From [dbo_XXXX Main Data Page];"
    instead of:
    Code:
    "From dbo_[XXXX Main Data Page] ;"
    Have a nice day!

  9. #9
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    I have made the changes suggested, I believe I still have a syntax issue. Note in my original post prior to this one I spotted the first mistake (- vs _)

    I have changed the
    [Code]
    "FROM [dbo_XXX Main Data Page] ;" (there is a space between the last bracket and the semi colon ; no space between semicolon and ", but I have also tried variations thinking it was a spacing issue. Still getting "Query input must contain at least one table or query" on the error message.

    Wow, one would think this would be easier than it is.

    Just so I am clear: I am writing this module in an MS Access 2007 database that has a file extension of accdb and has a linked table to SharePoint with the name of XXXX Main Data Page. 1) The goal is to "export" data to a new MS Access DB in the same folder and create a table in the new database where a static copy of the data will be stored. The procedure will create the new db and table.

    Yes?

    Thanks and sorry for this...
    Fighting Terror One Query @ a Time

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I work from home this week, where I don't have a Sharepoint server installed, so it's hard to test in exactly the same situation as yours. However, I made a simulation with what's probably the closest setting: an Access 2007 database with an attached table to a SQL Server. Everything works as expected.

    I believe that there's a glitch in the way the query string is built. Probably not a "true" syntax error (a similar query works here) but something must mistyped, somewhere.

    A way to test it would consist in copying to query string and pasting it into the SQL view of a new query. You'll most certainly receive the same error message, but sometimes it's more explicit and the offending part is highlighted, which would help in understanding what's wrong:
    Code:
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
    '    CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    Debug.Print Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    Stop
    ' Open the immediate window and copy/paste the SQL sentence into a new query.
    I'll keep searching and let you know if I can come up with something.

    Quote Originally Posted by jstpierre View Post
    Thanks and sorry for this...
    No need to be sorry, you're welcome!
    Have a nice day!

  11. #11
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    I am thinking that we are not on the same page. I can create Make Table Query and it creates a duplicate table with all of the values in the linked table into a static table within the same db.

    What I am trying to do is the same thing, but export the results just like we have it designed here into a new DB and a new table for that day (or rather point in time). From Access to Access. It may or may not be incidental, that the source table in question is linked to SharePoint where it is getting it's values from.

    Does this help? Again thanks in advance,
    Jim
    Fighting Terror One Query @ a Time

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    We're tuned on the sam wavelength, if I may say so and I don't see any reason why it should not work. I'm joining a zipped copy of my test database. You won't be able to backup the attached table, of course, but I also included a function to backup a local copy of the same table. Maybe you'll be able to point out the differences between your version of the procedure and mine.

    Could you please re-post the whole procedure as it is in your database right now? Maybe I'll be able to figureout what's wrong. In the mean time could you also check that the line preceding the offending one is able to actually create a new database file?
    Attached Files Attached Files
    Have a nice day!

  13. #13
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sinndo,

    Thanks. I will have to download your db from another location.

    Here is what I have configured so far. Suffice it to say that yes I have had to edit field names etc and they are represented with xxxs

    Code:
    Option Compare Database
    
    Sub Backup_Tbl_Jxxx_Main_Data_Page()
    
    
    
        Const c_DbName As String = "D:\Documents and Settings\james.Name\My Documents\BackUp_@M.mdb"
        Const c_SQL As String = "SELECT ID, [XXXX or OPEN PR Number], [POC's Name], [POC Phone#],[Date Received], [Requesting Org], [Requirement Type], " & _
                                       "[Cost Estimate], Description, Comments,[Date to XXXX], Status, [RCC OFFICE],[Contract Award Date], [Contract Number], XXX, [Deliver date or POP Start Date], xxx, [Requestor's Email Address],  " & _
                                       "[POP END Date], [xxx Certs], SDN, [Date to RM], [Date to xxx], [Date to xxx], [Date to xxx], [Date to Contracting], [xxx Name], [xxx Unit], [xxx Phone], [COR Rotation Date], xxx106, xxx206, xxx003, xxx222, TIP, [COR Email], " & _
                                       "[xxx Vote], [xxx Vote], [xxx Vote], [xxx Vote],[xxx Vote], [xxx xxx Vote], [xxx Comments], [xxx Comments], [xxx Comments], [xxx Comments], [xxx Comments], [xxx xxx Comments], [xxx xxx CERT], Option, [Option Period Begin], " & _
                                       "[Option Period End], E-xxx, [xxx Vote], [xxx Comments], [Reason for Action], [Facts or Discussion], [Recommendation], [SJA Comments], [E-xxx URL], [xxx Advisory], [Short Title of Request] " & _
                                "INTO @T IN @D " & _
                                "FROM [dbo_Jxxx Main Data Page]; "
        Dim strDbName As String
        Dim strTableName As String
        
        strDbName = Replace(c_DbName, "@M", Format(Date, "yyyy_mm"))
        strTableName = "Tbl_JxxxDBBACKUP_" & Format(Now, "mm_dd_hh_nn")
        If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDbName, dbLangGeneral
        CurrentDb.Execute Replace(Replace(c_SQL, "@T", strTableName), "@D", strDbName), dbFailOnError
    The line highlighted in yellow is the
    Code:
     If Len(Dir(strDbName)) = 0 Then DBEngine.CreateDatabase strDBName, dbLangGeneral
    Still getting the message Query input must contain at least one table or query.

    Jim
    Fighting Terror One Query @ a Time

  14. #14
    Join Date
    Nov 2011
    Location
    Currently in Kabul Afghanistan
    Posts
    47
    Sorry, the line above represents the line above the offending line.
    Fighting Terror One Query @ a Time

Posting Permissions

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