How do I copy customers from an ODBC to an OLEDB

[Tony Girgenti]

Hello.

I'm trying to copy customers from an "ODBC connection to QuickBooks" to an
"Access MDB" table called "QB CUSTOMERS".

The first thing i do is delete the customers from the Access table using an
SQL String as "DELETE FROM [QB CUSTOMERS]". That works OK.

Next, i connect to the ODBC Quickbooks customer table and insert the
customer table FullName field into the "QB CUSTOMERS" table using an SQL
String as "INSERT INTO [QB CUSTOMERS] (CUSTOMER) SELECT FullName FROM
QODBC.[QuickBooks Data].Customer;"

In a Try/Catch, it blows up on the statement
"qbCustomersODBCCommand.ExecuteNonQuery()" and i use a message box to
display the "System.Data.Odbc.OdbcException" message string. The error
message is "Error [42000] [QODBC] Expected lexical element not found:
SP_REPORT"

Does anybody know how to do what i am trying to accomplish ?

Any help would be gratefully appreciated.

Thanks,
Tony

 

[Paul Clement]

¤ Hello.
¤
¤ I'm trying to copy customers from an "ODBC connection to QuickBooks" to an
¤ "Access MDB" table called "QB CUSTOMERS".
¤
¤ The first thing i do is delete the customers from the Access table using an
¤ SQL String as "DELETE FROM [QB CUSTOMERS]". That works OK.
¤
¤ Next, i connect to the ODBC Quickbooks customer table and insert the
¤ customer table FullName field into the "QB CUSTOMERS" table using an SQL
¤ String as "INSERT INTO [QB CUSTOMERS] (CUSTOMER) SELECT FullName FROM
¤ QODBC.[QuickBooks Data].Customer;"
¤
¤ In a Try/Catch, it blows up on the statement
¤ "qbCustomersODBCCommand.ExecuteNonQuery()" and i use a message box to
¤ display the "System.Data.Odbc.OdbcException" message string. The error
¤ message is "Error [42000] [QODBC] Expected lexical element not found:
¤ SP_REPORT"
¤
¤ Does anybody know how to do what i am trying to accomplish ?
¤
¤ Any help would be gratefully appreciated.

That doesn't look like a valid ODBC connection string for your Quickbooks file. If you can create a
linked Access table (File...Get External Data...Link Tables) to this file then you can retrieve the
necessary connection string information when the linked table is in design mode.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Tony Girgenti]

Hello Paul.

I'm not sure of what you mean by this. Is there a connection string in my
post?
I essentially got the connection string from Server Explorer in VS.NET
instead of from the Access Link Manager, as you suggested.

I don't think i'm having a problem connecting. The .Open works and i can do
a .ExecuteReader and cycle through the records without a problem.

Thanks,
Tony

On Tue, 12 Dec 2006 08:57:39 -0500, "Tony Girgenti"

¤ Hello.
¤
¤ I'm trying to copy customers from an "ODBC connection to QuickBooks" to
an
¤ "Access MDB" table called "QB CUSTOMERS".
¤
¤ The first thing i do is delete the customers from the Access table using
an
¤ SQL String as "DELETE FROM [QB CUSTOMERS]". That works OK.
¤
¤ Next, i connect to the ODBC Quickbooks customer table and insert the
¤ customer table FullName field into the "QB CUSTOMERS" table using an SQL
¤ String as "INSERT INTO [QB CUSTOMERS] (CUSTOMER) SELECT FullName FROM
¤ QODBC.[QuickBooks Data].Customer;"
¤
¤ In a Try/Catch, it blows up on the statement
¤ "qbCustomersODBCCommand.ExecuteNonQuery()" and i use a message box to
¤ display the "System.Data.Odbc.OdbcException" message string. The error
¤ message is "Error [42000] [QODBC] Expected lexical element not found:
¤ SP_REPORT"
¤
¤ Does anybody know how to do what i am trying to accomplish ?
¤
¤ Any help would be gratefully appreciated.

That doesn't look like a valid ODBC connection string for your Quickbooks
file. If you can create a
linked Access table (File...Get External Data...Link Tables) to this file
then you can retrieve the
necessary connection string information when the linked table is in design
mode.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Paul Clement]

¤ Hello Paul.
¤
¤ >> That doesn't look like a valid ODBC connection string for your Quickbooks
¤ >> file. <<
¤
¤ I'm not sure of what you mean by this. Is there a connection string in my
¤ post?
¤ I essentially got the connection string from Server Explorer in VS.NET
¤ instead of from the Access Link Manager, as you suggested.
¤
¤ I don't think i'm having a problem connecting. The .Open works and i can do
¤ a .ExecuteReader and cycle through the records without a problem.
¤

What does your Open statement look like? I'm unfamiliar with the ODBC connection string for
Quickbooks.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Tony Girgenti]

Hello Paul.

Here is the code to open and cycle through the customer rows in the
Quickbooks Customer table.

qbCustomersConnection.Open()
qbCustomersReader = qbCustomersODBCCommand.ExecuteReader()
While qbCustomersReader.Read()
MessageBox.Show("Name = " & qbCustomersReader.Item("FullName").ToString)
End While

This works OK. It's just the insert that i'm not sure of how to accomplish
using the "qbCustomersODBCCommand.ExecuteNonQuery()" statement.

Thanks,
Tony

 

[Paul Clement]

¤ Hello Paul.
¤
¤ Here is the code to open and cycle through the customer rows in the
¤ Quickbooks Customer table.
¤
¤ qbCustomersConnection.Open()
¤ qbCustomersReader = qbCustomersODBCCommand.ExecuteReader()
¤ While qbCustomersReader.Read()
¤ MessageBox.Show("Name = " & qbCustomersReader.Item("FullName").ToString)
¤ End While
¤
¤ This works OK. It's just the insert that i'm not sure of how to accomplish
¤ using the "qbCustomersODBCCommand.ExecuteNonQuery()" statement.
¤
¤ Thanks,
¤ Tony

OK, but I don't see the connection string you are using. Can you post that?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Tony Girgenti]

Hello Paul.

Here is the connection string.
Dim qbCustomersConnectionString As String = "DSN=QuickBooks
Data;UseDCOM=Y;OptimizerDBFolder=C:\Program Files\QODBC Driver for
QuickBooks\Optimizer;DFQ=C:\Documents and Settings\Administrator\My
Documents\Ut\QB\sample_service-based business.qbw;SERVER=QODBC"

Thanks,
Tony

 

[Paul Clement]

¤ Hello Paul.
¤
¤ Here is the connection string.
¤ Dim qbCustomersConnectionString As String = "DSN=QuickBooks
¤ Data;UseDCOM=Y;OptimizerDBFolder=C:\Program Files\QODBC Driver for
¤ QuickBooks\Optimizer;DFQ=C:\Documents and Settings\Administrator\My
¤ Documents\Ut\QB\sample_service-based business.qbw;SERVER=QODBC"
¤
¤ Thanks,
¤ Tony

Do you know if it's possible to use a DSN-less connection?

For example, a DSN-less ODBC connection for Access would look something like the following:

[MS Access;DATABASE=c:\test files\db1 xp.mdb;]


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Tony Girgenti]

Hello Paul.

I searched the manual and the web site for that information. I don't think
that is possible.

Thanks,
Tony

 

[Paul Clement]

¤ Hello Paul.
¤
¤ I searched the manual and the web site for that information. I don't think
¤ that is possible.
¤

In that case you would probably have to create two Connections and two Datasets/DataTables - one for
the Access database and one for Quickbooks. Then you could insert, line by line, from the Quickbooks
DataTable to the Access DataTable and then perform the update when completed. It's much slower but
it will work.


Paul
~~~~
Microsoft MVP (Visual Basic)