Sage 100

Have never attempted a VI import from an Access DB.  Call me crazy for trying now.

I created an ODBC driver for the import file.  When trying a query from Excel, I get the same basic message.  Thinking it may be a driver set up issue.  Using a 32 bit driver.

Thoughts are appreciated.




------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Can you post the SQL statement?

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

@Kevin Moyes - Sorry, you lost me at the word create.......

The only thing in Access I can do is create a pass through query.  If I step off that path and I fall into the abyss.  @David Speck II, the SQL is in the bottom panel.  What I am trying to do is import two fields from an Excel file.  Easy peasy, create a tab delimited text file.  Problem is that the fields can have up to 500 characters.  My initial text import produced random results (some fields were completely incorrect).  So I thought maybe Access.  I imported the Excel data into Access and ended up here.  I think I will revisit the Text file to see where the wheels came off the bus there.  TIA


​​​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 09-02-2020 09:50
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Column names with spaces are always a bad idea.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 09-02-2020 10:48
From: Jeff Schwenk
Subject: VI import from Access DB - v2016

@Kevin Moyes - Sorry, you lost me at the word create.......

The only thing in Access I can do is create a pass through query.  If I step off that path and I fall into the abyss.  @David Speck II, the SQL is in the bottom panel.  What I am trying to do is import two fields from an Excel file.  Easy peasy, create a tab delimited text file.  Problem is that the fields can have up to 500 characters.  My initial text import produced random results (some fields were completely incorrect).  So I thought maybe Access.  I imported the Excel data into Access and ended up here.  I think I will revisit the Text file to see where the wheels came off the bus there.  TIA

​​​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------


Original Message:
Sent: 09-02-2020 09:50
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

If you copy that SQL statement and then start a new query in the target access database and paste in the SQL statement, will it run?

I did a quick test in access and it will not accept wrapping a column in double quotes, you have to use either the grave accent (`) or left and right square brackets.


------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 09-02-2020 10:53
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Column names with spaces are always a bad idea.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 09-02-2020 10:48
From: Jeff Schwenk
Subject: VI import from Access DB - v2016

@Kevin Moyes - Sorry, you lost me at the word create.......

The only thing in Access I can do is create a pass through query.  If I step off that path and I fall into the abyss.  @David Speck II, the SQL is in the bottom panel.  What I am trying to do is import two fields from an Excel file.  Easy peasy, create a tab delimited text file.  Problem is that the fields can have up to 500 characters.  My initial text import produced random results (some fields were completely incorrect).  So I thought maybe Access.  I imported the Excel data into Access and ended up here.  I think I will revisit the Text file to see where the wheels came off the bus there.  TIA

​​​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 09-02-2020 09:50
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

@David Speck II ​ - Thanks

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------

Original Message:
Sent: 09-02-2020 12:05
From: David Speck II
Subject: VI import from Access DB - v2016

If you copy that SQL statement and then start a new query in the target access database and paste in the SQL statement, will it run?

I did a quick test in access and it will not accept wrapping a column in double quotes, you have to use either the grave accent (`) or left and right square brackets.

When queried from Crystal Reports through the DAO Access/Excel driver, CR uses grave accents as default.

Whereas if you use Access' query design and add the table and its columns, Access uses left and right square brackets as default.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 09-02-2020 10:53
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Column names with spaces are always a bad idea.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-02-2020 10:48
From: Jeff Schwenk
Subject: VI import from Access DB - v2016

@Kevin Moyes - Sorry, you lost me at the word create.......

The only thing in Access I can do is create a pass through query.  If I step off that path and I fall into the abyss.  @David Speck II, the SQL is in the bottom panel.  What I am trying to do is import two fields from an Excel file.  Easy peasy, create a tab delimited text file.  Problem is that the fields can have up to 500 characters.  My initial text import produced random results (some fields were completely incorrect).  So I thought maybe Access.  I imported the Excel data into Access and ended up here.  I think I will revisit the Text file to see where the wheels came off the bus there.  TIA

​​​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 09-02-2020 09:50
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

If  you haven't fallen into the abyss yet: It appears that you are trying to use Excel to summarize your source data. Have you considered summarizing the source text file (linked I assume) with an Access Cross-Tab query and importing from that? We could then call you Mr Cross-Tab in addition to Mr Pivot Table.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 09-02-2020 13:35
From: Jeff Schwenk
Subject: VI import from Access DB - v2016

@David Speck II ​ - Thanks

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444
------------------------------


Original Message:
Sent: 09-02-2020 12:05
From: David Speck II
Subject: VI import from Access DB - v2016

If you copy that SQL statement and then start a new query in the target access database and paste in the SQL statement, will it run?

I did a quick test in access and it will not accept wrapping a column in double quotes, you have to use either the grave accent (`) or left and right square brackets.

When queried from Crystal Reports through the DAO Access/Excel driver, CR uses grave accents as default.

Whereas if you use Access' query design and add the table and its columns, Access uses left and right square brackets as default.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 09-02-2020 10:53
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Column names with spaces are always a bad idea.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 09-02-2020 10:48
From: Jeff Schwenk
Subject: VI import from Access DB - v2016

@Kevin Moyes - Sorry, you lost me at the word create.......

The only thing in Access I can do is create a pass through query.  If I step off that path and I fall into the abyss.  @David Speck II, the SQL is in the bottom panel.  What I am trying to do is import two fields from an Excel file.  Easy peasy, create a tab delimited text file.  Problem is that the fields can have up to 500 characters.  My initial text import produced random results (some fields were completely incorrect).  So I thought maybe Access.  I imported the Excel data into Access and ended up here.  I think I will revisit the Text file to see where the wheels came off the bus there.  TIA

​​​

------------------------------
Jeff Schwenk
FORMER 90M Board Member
Bottomline Software, Inc.
Waynesboro VA
540-221-4444


Original Message:
Sent: 09-02-2020 09:50
From: Kevin Moyes
Subject: VI import from Access DB - v2016

Instead of a complicated ODBC query, create a Query object within the MS Access database to make the ODBC easier.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON