Unanswered: Issue with Linked Table copy to new DB, need VB Tweek Please
I almost have it and need a tweek. I am getting a RT error "3838" Multi-Valued fields are not allowed in SELECT INTO statements.
MS Access 2007 linked to SharePoint ( a single table from SharePoint is mirrored in Access)
Goal: Create new DB with a name that is formatted by Year and Date Created. Copy existing linked table and values into new DB and DO NOT CONNECT TO SHAREPOINT. I need a stand alone copy. I thought I had it figured out till I started trying to disable the strLinkName.
I was able to perform the above actions, thanks in main part to the kindness and patience of Sinndo!! Create the DB and copy the table but when opening the "new stand alone db" it sync'd back up to SharePoint. Which defeated the purpose of a snapshot in time.
Here is the VB Script as I have it now and I need a tune up please:
'Run-time error '3838': Multi-valued fields are not allowed in SELECT INTO statements.' is the error returned.
Dim ws As Workspace
Dim db As Database
Dim LFilename As String
Dim strLinkName As String
Dim strSQL As String
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
'Path and file name for new mdb file
LFilename = "D:\Documents and Settings\my.name\My Documents\" & Replace("db3_@M.mdb", "@M", Format(Date, "yyyymmdd"))
'Name of table to be copied
strLinkName = "JAMB Main Data Page"
'Make sure there isn't already a file with the name of the new database
If Dir(LFilename) <> "" Then Kill LFilename
'Create a new mdb file
Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
'Copy Linked table into a new table along with its data
strSQL = "SELECT [" & strLinkName & "].* INTO [" & strLinkName & "] IN '" & LFilename & "' FROM [" & strLinkName & "]"
Set db = Nothing
Thanks in advance for your guidance.
Fighting Terror One Query @ a Time