Access 2003: TransferDatabase action help

Posted on 2006-04-13
Last Modified: 2008-01-09
I am working on linking a table in Access to a Sharepoint List.  Deep within the bowels of access(!) I found the Macro action, "TransferDatabase".

Here are the Action Arguments:

Transfer Type:   Link
Database Type:  WSS
Database Name:  http://gaisvr1/Team1;LIST=INS1;View=All Items;RetrieveIds=yes
Object Type:  Table
Source:  INSTemp
Destination: INS1
Structure:  No

Here's the problem:  Even though all these "arguments" appear to be correct, I get an error "The Microsoft Jet database could not find the object 'INSTemp'.  Makes sure the object exists and that you spell its name and path correctly."

The INSTemp table DOES exist, and IS spelled correctly.

Any help would be appreciated!  Thanks-

Question by:cepes
    LVL 44

    Expert Comment

    I'm running A2K on XP Home and I do not have a Database Type: WSS so I cannot help you.  Strangely, when I click on the URL beside Database Name:  I get taken to:

    Maybe that's not strange - but I was surprised.
    LVL 39

    Accepted Solution

    Did you follow the help file on this ... it has very specific info on the database name argument ...

    <Access help>
    The easiest way to obtain this information is to perform the following steps:

    In the database window, create a new table that links to the Windows SharePoint Services list.
    Open the linked table in Design view.
    Click Properties on the View menu.
    The required string is listed in the Description property.

    </Access help>

    it looks like you have it right but please dbl check. It may be that your VIEW name has a space, try removing that or try adding single or dbl quotes around the actual name ...   'All Items'


    Author Comment

    Thanks for your help but I think I may hve to switch to VBA to accomplish this link between Access and WSS.  Below I have pasted the MSDN Help page.  Given that my Access table is named "INSTemp" and my Sharepoint List is named "INS1", I need help setting up an "Export" action as first.  If that works, I'll work on the "Link" action, which I see is a bit harder.

    The MSDN site at says:

    Programming SharePoint access

    Traditionally, one of the strengths of Access has been that everything you could do from the user interface could also be done from VBA code. That's still true when working with SharePoint but, as I'll show you in a moment, there's one little complication.

    As with other data transfer operations, you use the DoCmd.TransferDatabase method to move data to and from SharePoint programmatically. To export a table named Orders to a SharePoint list named OrdersList, you can use a command like this:

    DoCmd.TransferDatabase acExport, "WSS". _
     "http://Server/sites/testsite", acTable, "Orders", _
     "OrdersList", False

    The final parameter controls whether to export only the structure of the structure, or export both the structure and the data from the table.

    To import or link a SharePoint list, you can also use the TransferDatabase method—but this is where the complication comes in. Here's a VBA command to link a SharePoint list named Parts:

    DoCmd.TransferDatabase acLink, "WSS", _
     "WSS;HDR=NO;IMEX=2;" & _
     "DATABASE=http://Server/sites/testsite;" & _
     "LIST={800BE2B7-FA3C-4CFC-BBB3-8500C4EDCF22};" & _
     "VIEW=;RetrieveIds=Yes;TABLE=Parts", acTable, , _

    Ugly, isn't it? The problem is that SharePoint doesn't refer to lists by name. Internally SharePoint uses GUIDs to identify lists (that's how it can store, for example, 10 different Contacts lists in the same MSDE database). When you use the Access user interface to import or link SharePoint data, Access takes care of matching the names to the GUIDs. But when you're dealing with the Jet engine directly through VBA, you're on your own. The long string (starting with "WSS" and ending with "TABLE=Parts") identifies a particular SharePoint list.

    If you're faced with the problem of programmatically importing or linking SharePoint data, there are two ways to get the correct GUID. The easy way is to manually link the desired list to an Access database, and then open the linked table in design view—the Description property of the linked table will contain the necessary linking string. Obviously this is less than useful when you need to be able to link to arbitrary lists, though. In that case, you can use the Web Service interface to SharePoint, which exposes a Lists.GetList method. This method takes the name of a list on a particular site and returns detailed information, including the GUID that identifies the list
    LVL 39

    Expert Comment

    The MSDN suggests the same as Access help ... create 1 time manually and then you have the detail necessary to create the same link through code, of course if you have done it manually whjy you would need to do it again in code I'm not sure.

    Have you tried their suggestion?


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now