Insert multiple lines of values by enumerating with a "#"?

[mcucino]

I have never seen SQL like this and the ODBC software I'm using says it should work. The destination table has a field called "JournalLine_LineAmount" and supposedly Access can parse this SQL into multiple lines of data, however I get a syntax error every time I try. I mostly want to know if anyone has ever seen this notation before and if so, do you know how to get past the syntax error?

INSERT INTO ManualJournals (Narration, JournalLine_LineAmount#1, JournalLine_AccountCode#1, JournalLine_LineAmount#2, JournalLine_AccountCode#2, JournalLine_LineAmount#3, JournalLine_AccountCode#3, JournalLine_LineAmount#4, JournalLine_AccountCode#4, JournalLine_LineAmount#5, JournalLine_AccountCode#5) VALUES ('Accrued expenses', ‘30’, '200', '-20', '310','10','425','-10','429','-10','310');

[June7]

That would save one record, not multiple records. There would have to be fields with those names (a non-normalized structure). Where did you read this would create multiple lines of data?

If fields are number type, do not use apostrophe delimiters.

[CJ_London]

# is not a character to be used in table and field names, try putting square brackets around the name, it sometimes helps. e.g [JournalLine_LineAmount#1]

supposedly Access can parse this SQL into multiple lines of data

nope - no sql will create multiple lines in this way

the ODBC software I'm using says it should work

can you provide a link that explains this

you say you are using ODBC software - please clarify what you mean. ODBC usually means Open Data Base Connectivity and is not software but a means to connect to a database

[mcucino]

I agree it sounds crazy, but here are the docs, straight from the ODBC developer with data structures and examples:

http://cdn.cdata.com/help/DXC/odbc/p...aljournals.htm

[June7]

What are CData and Xero?

[mcucino]

Cdata is the software company that makes an ODBC to Xero (which is an accounting software)

[orange]

@mcucino
Are you using or trying to use Xero with MS Access?
Have you contacted Xero? Or CData for working examples/tutorials?

I am not familiar/never heard of Xero.

[June7]

Still not clear what you mean by ODBC software. ODBC is a method of connecting to database. Access connects to Oracle or SQLServer via ODBC. Same for other frontend apps like VB.net.

The SQL engine in Access cannot process that statement in the manner described.

[CJ_London]

I've heard of Xero - it's the newish kid on the block for online accounting software. One of my clients uses it although I have not had any involvement myself. However the implication from the documentation is that the data is held on your servers, not online, so leaves me a bit confused.

@mcucino - your link makes things clearer. the requirement is to use an ODBC API which utilises a SQL style statement as a means of updating Xero Manual Journal tables, not Access ones. And from the documentation, the sql you quote only applies to manual journals (being one type of transaction in the application) - in other systems the data is usually supplied (more traditionally) in rows from a .csv or .txt file and passed through an import process. Reading through the documentation, what does surprise me is that when linked to a Xero table, you can make changes from Access - 'changes made to it will be reflected back to the underlying table in Xero.' I would think that could be really dangerous but I don't know enough about Xero to be sure.

Your error is the values (30, -20,etc), they are numeric and should not be surrounded by single quotes - check the documentation you provided and you will see. You may also need to include the decimals (ie. 30.00), depends how fussy Xero is.

[mcucino]

ODBC is a method of connecting to a database, correct. There are various drivers that can allow users to use ODBC when it's not natively available in the software in question. In this case, that software is Xero. In other cases, it may be quickbooks. You cannot connect the data in Access directly to quickbooks without an ODBC driver such as QODBC. I hope that makes sense. If not, I encourage you to check out Cdata's website, https://www.cdata.com/odbc/ because they offer over 100 different drivers that allow you to use sources that otherwise would not be compatible using ODBC.

Still not clear what you mean by ODBC software. ODBC is a method of connecting to database. Access connects to Oracle or SQLServer via ODBC. Same for other frontend apps like VB.net.

The SQL engine in Access cannot process that statement in the manner described.

[mcucino]

Thanks, I tried it both ways (with and without quotes) because the email from Cdata support instructed me to use the quotes. It still doesn't help. I will try it with decimal places, but I think the issue is that it's not recognizing the enumerated field names. I put them in brackets as suggested above and it got past the initial syntax error, but now the error message states "unknown field name". It's very frustrating, considering how helpful their support team has been. They showed me step by step screenshots of using this exact query in Access and the output it produces, so I am at a loss for how to proceed.

I've heard of Xero - it's the newish kid on the block for online accounting software. One of my clients uses it although I have not had any involvement myself. However the implication from the documentation is that the data is held on your servers, not online, so leaves me a bit confused.

@mcucino - your link makes things clearer. the requirement is to use an ODBC API which utilises a SQL style statement as a means of updating Xero Manual Journal tables, not Access ones. And from the documentation, the sql you quote only applies to manual journals (being one type of transaction in the application) - in other systems the data is usually supplied (more traditionally) in rows from a .csv or .txt file and passed through an import process. Reading through the documentation, what does surprise me is that when linked to a Xero table, you can make changes from Access - 'changes made to it will be reflected back to the underlying table in Xero.' I would think that could be really dangerous but I don't know enough about Xero to be sure.

Your error is the values (30, -20,etc), they are numeric and should not be surrounded by single quotes - check the documentation you provided and you will see. You may also need to include the decimals (ie. 30.00), depends how fussy Xero is.

[mcucino]

EUREKA! I knew posting to the forum would help. Even if it's just because I had to reread the support email once I screenshotted it. There is one subtle difference in his query. I was attempting to operate on a linked table directly in Access whereas he was using a passthrough query. I'm not sure how dumb of a mistake that was on my part - but it finally works!

[CJ_London]

he was using a passthrough query

that was going to be my next suggestion. - and your query still has quotes around some of the numbers