Hi,

This is probably a very basic question – sorry!

I have previously used GeniusConnect to link my Outlook contacts to MS Access, with great success. I’d now like to go back to basics and link all my Outlook contacts to a new Excel worksheet, called Sheet1. All I want to do initially is to dump everything in my Contacts folder into the new Excel worksheet. I’m using Excel 2003 and GeniusConnect 3.0.1.0SP1.

I have created a column called “Ident” in the worksheet called “Sheet1” and made Ident the primary key. My SQL statement is:

“select max(Ident) + 1 from Sheet1$”. When I test the SQL, I get the following error:

“37000 [Microsoft][ODBC Excel Driver] Syntax error in FROM clause. (-3506)”

Can you please tell me what I’m doing wrong?

Thanks in advance.

I think the problem is in the table name.

try somethink like:
select max(Ident) + 1 `Sheet1$my excel file name`

You can see an example in Filter rows (Assign Tabel dialog->Filter rows), to test is
just enter 1=1 as filter.

Hi,

I tried that but it didn't work. Could you please give me a bit more detail on where I can find the example? Thanks.

You have to use the same table name you can see in the Assign Table Dialog -> Table Combobox or example SQL query in Filter rows dialog.

ODBC Excel driver is using "Sheetname file name" combination as Table Name.

I’m still not getting it. The Assign Table Dialog -> Table Combobox shows table name “AA$” (AA is the name of the worksheet). If I go to Filter Rows and enter 1=1 as filter, the result shows the correct table, with the correct column headings. I can’t see the Excel file name in any of these comboboxes and I get the same error message if I manually enter it in the SQL statement.

OK, maybe I have a different ODBC Excel driver.

The filter Dialog has 2 edit fields, one is gray (read only) and shows the result query for a filter.
IF the 1=1 filter works you can use the table name as in the example.
When I enter 1=1 as filter, the gray edit field shows:
select * from `Sheet1$test-sheet` where 1=1.

So I can use the Table Name : `Sheet1$test-sheet`
in any query and it works.

I'm using Microsoft Excel Driver version:4.00.6304.00

I'm using the same Microsoft Excel Driver: version:4.00.6304.00

When I enter 1=1 as filter, the “current Param” (grey) value shows 1=1

The white edit field shows ‘1=1’ and the grey edit field shows:

select * from `AA$` where '1=1'

So something is happening differently. Could it be something more basic?

Ok, try to use `AA$` (including the ' chars)as table name, if you still get the error, check the Max syntax

It looks as though the SQL Max syntax is at fault, but I can't see where. I've tried a number of options for the part after the "From" operator, with the following results:

~~~~~~
select Max(Ident) + 1 from 'AA$'

S1000 [Microsoft][ODBC Excel Driver] Syntax error in query. Incomplete query clause. (-3003)

~~~~~~

select Max(Ident) + 1 from AA

S0002 [Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object “AA”. Make sure the object exists and that you spell its name and the path name correctly. (-1305)

~~~~~~

select Max(Ident) + 1 from "AA$"

Runs, but shows Expr 1000

~~~~~~

select Max(Ident) + 1 from (AA$)

37000 Microsoft][ODBC Excel Driver] Syntax error in JOIN operation. (-3510)

~~~~~~

So what could be wrong with the syntax?

I think this one is OK.
select Max(Ident) + 1 from "AA$"

Expr 1000 is normal, you can name it your self:

select Max(Ident) + 1 as NewIdent from "AA$"

Is it returning somethink? If the excel is empty it will return null, you can add some dummy row with Ident 0

Hooray, we're getting somewhere! This seems to work fine. However, when I then go back to Outlook and do a "Store All", I get another error:

S1000 [Microsoft][ODBC Excel Driver] Operation must use an updeatable query. (-3035)(SyncFolder)

Is this connected?

Can you Set the SQL Trace On (GeniusConnect Config->Advance Admin options-see help file for Trace file location),
http://www.geniusconnect...les/GeniusConnect/2/3/3/  for support email)

Hi - I've sent you the trace file - I hope you can work out what's going on!