"Syntax error in FROM clause"

[ThomasB]

Hi

Using a pivot table in Excel to connect to a Table of external data in Access

ie I select get data from External Database/Access

microsoft query starts and everything works fine until I get to the final
section and when I push finish to display the data in Excel I get an error message

"Syntax error in FROM clause"

So I google this and get to the Microsoft website where I get the following explanation:

SYMPTOMS
When you use Microsoft Query to return external data to a worksheet in Microsoft Excel or when you view returned data in Microsoft Query, you may receive the following error message:
Syntax error in FROM clause.
Back to the top

CAUSE
This problem occurs if you attempt to retrieve data from a Microsoft Access or Microsoft Excel database, and the path to the database contains a period (.), for example:
C:\My.Test\Northwind.mdb
Back to the top

WORKAROUND
To work around this problem, change the path to the database so that it does not contain any periods, for example:
C:\MyTest\Northwind.mdb C:\My Test\Northwind.mdb


As I work on a network my filepath always has a "." between my first and surname
ie R:/Joe.Bloggs/DB1

So I find a location on the network where there is not a ". "contained in the path and put it there but get same error

So I put it on a memory stick and still the same error when I try to connect via the pivot table

Does anyone know what I am doing wrong?

Kind Regards

Thomas

 

[parry]

Hi Thomas, Ive recreated your problem and its annoying alright. I can only think of 2 options to resolve this.

1) If you have the ability to map a network drive (personally everything is locked down at my work so I cant but hopefully you can) then you can map your directory R:\Joe.Bloggs\DB1 to another letter, say Z. So now you can just browse to letter Z:\ and then select the file.

2) If you are unable to map the drive then I don't think you can use menu driven Excel connection method to the database. The following is some code I wrote that connects to a database. As you can see in the Conn.Open below this doesn't care whether the directory has a dot or not. You are going to have to know SQL so if you dont its not hard - see the web site http://www.w3schools.com/sql/sql_select.asp.

Sub ConnectToDatabase()
Dim Conn As Object, StrSQL As String, Rs As Object

'Connect to your database
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.Open "C:\Joe.Blogs\Db1\Db2.mdb" 'This is your path to the file

'Create SQL for the query yo need
StrSQL = "SELECT * FROM EMPLOYEE" 'This is the SQL of what data you want to return

'Open a recordset of the data you want
Set Rs = CreateObject("ADODB.Recordset")
Rs.Open StrSQL, Conn

'Put data into Excel
Worksheets("Sheet2").Range("A1").CopyFromRecordset Rs 'This is where the data is populated

'Close recordset and termintae connection to the database
Rs.Close
Conn.Close


End Sub

Good luck.

regards,
Graham

 

[ThomasB]

Hi Graham

Firstly apologies for not replying earlier, was away this weekend and have just logged into my computer.

Thank you very much for your help in this regard this certainly explains why it is not working and have saved me a few more hours banging my head against the keyboard while I try to work out what is going on.

Like yourself my company locks down the mapping of its drives so I think the ADO soloution is extremely viable (thank you).

Just one problem I cannot export the data from access to excel as it exceeds 65536 rows (using 2003) hence my reason for putting the pivot table data in an access table.

Show below is the code I would normally use to create a pivot table:

Set wsd = Worksheets("Over Days")
Worksheets("Over Days").Activate
finalrow = wsd.Cells(65536, 1).End(xlUp).Row
Set prange = wsd.Cells(5, 1).Resize(finalrow, 7)

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=prange.Address)
Set wsd = Worksheets("OLDEST")
Set pt = PTCache.CreatePivotTable(TableDestination:=wsd.Range("B8"), TableName:="PivotTable1")


Do you know if I could modify the vba code below:

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=prange.Address)

ie change "Source Type" to an access database and "SourceData" to an access table
maybe in combination with ADO?

If so have you any idea how the code would look?

Any help would be greatly appreciated

Best Wishes

Thomas

 

[parry]

Hi Thomas, if you look in the VBE Help under the topic CreatePivotTable Method there's an example in there on using an ADODB connection to an Access database. You need to create a connection to the Db and then when you add the PivotCache you use SourceType:=xlExternal and then set the Recordset property for the cache to the recordset you created during the connection process.

Heres the example code from the Help...
This example creates a new PivotTable cache using an ADO connection to Microsoft Jet, and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.

Sub VBEHelp_Example()
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
    .ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0"
    .Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
    .CommandText = "Select Speed, Pressure, Time From DynoRun"
    .CommandType = adCmdText
    .Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
    .CreatePivotTable TableDestination:=Range("A3"), _
        TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
    .SmallGrid = False
    With .PivotFields("Pressure")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Speed")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Time")
        .Orientation = xlDataField
        .Position = 1
    End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing

End Sub

 

[ThomasB]

Hi Graham

Thank you very much for the code, I cant tell you how much you have helped me sort this problem out.

Best Wishes

Thomas

 

[parry]

My pleasure Thomas, glad you have it sorted.