Undefined function "PlainText" in expression

[nuodadeile]

Hi,

I apologize in advance if I am not posting in the right place, I am not sure if it this is an Access or Excel issue.

I have data in Access which is imported from an ODBC Database. I have built queries in Access to handle the data, and in particular I have used the Access function "PlainText" to remove the HTML tags in some fields.
The query is running well in Access.

The problem occurs when I want to get the data from Access in an Excel sheet. I have used for this MS Query (Data > From Other Sources > From Microsoft Query > MS Access Database). When I add the table (query in access) that I want to import, I got the error: "undefined function 'PlainText' in expression"

I have looked for answers in many existing threads in several forums but couldn't find the solution. I think I have understood that this might be a "reference" issue. The thing is, I don't use any VBA code, only the Access PlainText function and I don't know which library I should activate or deactivate.

I am using Microsoft Office 2010.
I should add that my needs are only to get the formated Access table in Excel and to be able to update it from time to time. If there is another way that would get round this issue, I might consider it!

Thank you in advance for your help.

Kind regards,

Nuo

 

[RoryA]

I don't think MSQuery will accept PlainText (or Nz either) as it's purely an Access function. You could run a maketable query first and then extract the data from that table into your excel workbook using MSQuery.

 

[nuodadeile]

I don't think MSQuery will accept PlainText (or Nz either) as it's purely an Access function. You could run a maketable query first and then extract the data from that table into your excel workbook using MSQuery.

Thank you for the quick reply. It works indeed with the maketable query. The only problem is that the table is not automatically updated then? If I want to use the up-to-date database from the ODBC Database, I have to run the maketable query again in Access. I would need an "automated" solution that would use Excel only (user interface). Is that easily doable? Before I could use the Excel button "Refresh All" and I would get the table in Excel updated.

Thanks again!

Nuo

 

[xenou]

One way is like this:


First, in Access put a public subroutine in a standard module. This sub will run your make table query. It can be one of two styles, either raw sql (where you provide the sql text in your sub), or the name of a query (where you just provide the name of the saved query that is your make table query). Be sure to use the Public keyword for the sub.

Example - Code in ACCESS (sql text):

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] MakeTable_Table2_Version1()
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    s = s & " SELECT Table1.Title,"
    s = s & " Nz(Table1.Publication_Year,0) AS PubYear,"
    s = s & " myUDF() AS OtherField"
    s = s & " INTO Table2"
    s = s & " FROM Table1;"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Or, Code in ACCESS (saved query):

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] MakeTable_Table2_Version2()
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Query1"
    DoCmd.SetWarnings True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Next you run your subroutine from Excel:
Code in EXCEL:

[COLOR="Navy"]Sub[/COLOR] AccessMakeTable()
[COLOR="Navy"]Dim[/COLOR] appAccess [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] appAccess = CreateObject("Access.Application")
    [COLOR="Navy"]With[/COLOR] appAccess
        .OpenCurrentDatabase "C:\myTemp\db2.mdb", False
        .Run "MakeTable_Table2_Version1"
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    appAccess.Quit
    [COLOR="Navy"]Set[/COLOR] appAccess = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Edit: Note that you can kick of your refresh at the same time so your macro becomes your de facto "refresh all" command:

[COLOR="Navy"]Sub[/COLOR] MyRefreshMyTables()

    [COLOR="Navy"]Call[/COLOR] AccessMakeTable
    ActiveWorkbook.RefreshAll

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] AccessMakeTable()
[COLOR="Navy"]Dim[/COLOR] appAccess [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
    
    [COLOR="Navy"]Set[/COLOR] appAccess = CreateObject("Access.Application")
    [COLOR="Navy"]With[/COLOR] appAccess
        .OpenCurrentDatabase "C:\myTemp\db2.mdb", False
        .Run "MakeTable_Table2_Version1"
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    appAccess.Quit
    [COLOR="Navy"]Set[/COLOR] appAccess = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[nuodadeile]

Thank you very much xenou for the clear solution!
It works perfectly!

Best regards,

Nuo