Avatar of pdvsa
pdvsa
Flag for United States of America asked on

Importing tables with code - error message

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

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
pdvsa

8/22/2022 - Mon
peter57r

You have to use Recordset2 as the object type to deal with the new datatypes.

Dim rs as Dao.recordset2

I haven't looked any further at the code.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
pdvsa

ASKER
Peter:  let me know if you get a sec to look at it.  I think potential solution is the Dao.recordset2 but not sure how to fix.  If it is an easy fix please let me know...this functionality is not critical to the db.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
pdvsa

ASKER
OK I will delete those MVF in the tables.

thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jeffrey Coachman

In general, my biggest concern with using these "New" features is "Upgradability".

Many of these features are "MS Access ONLY", meaning there is no direct equivalent in MS SQL Server, MySQL, Oracle, ...etc
MVFs, Table LookUp fields, Hyperlinks, Attachments Datatypes, Rich/HTML text, OLE, ...all fall into this category at some level.

For example: Yes, MS SQL Server can store Image data, but you cannot take an Attachment field (full of images) and simply import it into an SQL table and have it work the same way.

See here under "Working with incompatible data types"


JeffCoachman
pdvsa

ASKER
good information.  A hyperlink is not a MVF is it?  
Nick67

No, but it is a field datatype peculiar to Access, and won't translate to SQL Server.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

To be fair, MS is always updating SQL Server.
Perhaps one day they may create a migration tool that will translate all of these Access specific features...
pdvsa

ASKER
FYI:
I removed the MVF (attachments) and hyperlinks as well but it still gave me the same error and the debugger still refered to tblEmployees once I hovered over the yellow highlight.  
I further deleted any fields that had a combo box with a row source qry under the lookup tab and I think that is what I needed to do to make this work.  

However, I have many tables with lookups on fields and therefore am not able to use this code.  

thanks for the help...
Nick67

This will export all the tables in a given db

Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Not tdf.Name Like "Msys*" Then
        DoCmd.TransferDatabase acExport, "Microsoft Access", Application.CurrentProject.Path & "\test.mdb", acTable, tdf.Name, "temp" & tdf.Name, False
    End If
Next tdf

MsgBox "Done!"

That will get them out of the current db into a portable one.
If they remain link tables, then you could use the Docmd.CopyObject to work them over

in the portable db

Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If  tdf.Name Like "temp*" Then
        DoCmd.CopyObject , tdf.name,acTable, right(tdf.name,len(tdf.name)-4)
        db.TableDefs.Delete "tdf.name"
    End If
Next tdf

You need to TEST this carefully, but it should work.
Your help has saved me hundreds of hours of internet surfing.
fblack61
pdvsa

ASKER
Nick, i appreciate your extra work on this.  I will test soon...