Use VB to mMake a local copy of a Sharepoint linked table

[crissyjo1]

I'm able to do a local copy manually, of course, but I have a need to copy the table structure locally. Using DoCmd.TransferDatabase results in creating a copy on the Sharepoint server.

Any ideas?

Thanks!

 

[Banana]

I'd think the simplest thing to do this is to do something like this:

Dim ltdf AS DAO.TableDef 'Local copy
Dim stdf DAO.TableDef 'SharePoint list
Dim lfld As DAO.Field
Dim sfld As DAO.Field

Set stdf = CurrentDb.TableDefs("<name of list>")
Set ltdf = CurrentDb.CreateTableDef

With ldf
  .Name = "..."
  For Each sfld In stdf.Fields
    Set lfld = ldf.CreateField(sfld.Name)
    'copy other required properties
   ...
 Next
 CurrentDb.TableDefs.Append ltdf
End With ltdf

...

 

[Mr. B]

You could create a Make Table type query to read data from your SharePoint linked list and create a local table. This way you can even include any ctitera in the query.

Then just execute the action query with VBA code:

Docmd.execute "NameOfYourQuery"

 

[crissyjo1]

You could create a Make Table type query to read data from your SharePoint linked list and create a local table. This way you can even include any ctitera in the query.

Then just execute the action query with VBA code:

Docmd.execute "NameOfYourQuery"

That would be so much easier. I'm assuming I need to use dbs.Execute instead though. However, I only want the structure, not the data. Any ideas on how to revise the query to address this?

Thanks!

 

[Mr. B]

I did not actually get the thought that you only wanted the table sturcture. If that is want you want, you might need to try Bananas method.

I suppose you could create the make table query and supply some sort of critera that would never return and records. When you execute this query it will create an empty table with all of the fields you include in your query with no records.

Just a thought.

 

[Banana]

Actually, I like Mr. B's idea much better.

I'm sure you should be able to do this:

SELECT *
INTO NameOfNewTable
FROM SharePointList
WHERE 1=0;

 

[Mr. B]

Just one more thought. If you have any "Multi-value" type fields in your SharePoint list (and there occasions where some of these are created by default by SharePoint when the list is created), you will not be able to use the "*" in the Make Table query as there is a restriction in Access. In this case you will have to specify the fields you want to have created in the new table.