troubleshooting Question

Importing tables with code - error message

Avatar of pdvsa
pdvsaFlag for United States of America asked on
Microsoft Access
13 Comments3 Solutions302 ViewsLast Modified:
Experts,

I typcially import tables into my db.
The reason is to remove the linked tables from the network and work the db from home.
I have some code to import tables and have placed it on an unbound form's button click event.
I get an error when importing.  

The error states "Multi Valued Fields are not Allowed in Select Statements". errorThe msg says something about the "Employees" table (yellow highlight in screen print)
This table contains hyperlinks and attachments.  
Maybe the code will not handle those type of fields.  

Any idea why I might receive the error?
I am running 2k7.  
Private Sub cmdTables_Click()

    Dim strTableName As String
    Dim rstLinkedTables As DAO.Recordset
    Dim strSQL As String
    Dim strYourDBYourPath As String
strYourDBYourPath = "C:\Documents and Settings\Ajohnso01\Desktop\data.accdb"

Set rstLinkedTables = CurrentDb.OpenRecordset("Select * FROM msysobjects WHERE Type=6")
rstLinkedTables.MoveFirst
    
    Do While Not rstLinkedTables.EOF
        strSQL = "SELECT " & rstLinkedTables!Name & ".* INTO " & "[" & rstLinkedTables!Name & "]" & " In " & "'" & strYourDBYourPath & "'" & " FROM " & "[" & rstLinkedTables!Name & "]"
        CurrentDb.Execute strSQL, dbFailOnError
        rstLinkedTables.MoveNext
    Loop

rstLinkedTables.Close
Set rstLinkedTables = Nothing


End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros