Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
When I use Linked tables in MS Access the ODBC link locks-up? |
Author |
Message |
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-02-21 12:22:16 |
When I use Linked tables in MS Access 2003, the ODBC link locks-up QuickBooks and Access and it then complains about the link being lost (this is normally an hour or so after the attempt to open the table). How do I fix this? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-02-21 12:22:45 |
As far as Linked tables are concerned, most MS Access developers use the "LinkODBC" sub procedure modules for their connections as they are compiled and are far more stable.
Start a new Blank Database, click on Modules, we are going to create a InvoiceLine Connect Module as shown here. Click on "New":-
and paste the following code and save it as "InvoiceLine Connect Module" as shown below :
Option Compare Database Option Explicit
Public Sub LinkODBC() Dim tdfAccess As TableDef Dim dbsODBC As Database Dim strConnect As String
strConnect = "ODBC;DSN=QuickBooks Data;OLE DB Services=-2;"
Set dbsODBC = OpenDatabase("", False, False, strConnect)
Set tdfAccess = CurrentDb.CreateTableDef("InvoiceLine", dbAttachSavePWD) tdfAccess.Connect = dbsODBC.Connect tdfAccess.SourceTableName = dbsODBC.TableDefs("InvoiceLine").Name CurrentDb.TableDefs.Append tdfAccess
dbsODBC.Close
Set dbsODBC = Nothing Set tdfAccess = Nothing End Sub
Once you "Run Sub/UserForm", the "InvoiceLine" table will be available to use in your Access mdb.
|
|
|
|
|
Posted : 2006-11-23 03:56:44 |
Is there a way I can cut and paste your code? When I try to select text on this FAQ my system won't let me.
Thanks! |
|
|
|
|
Posted : 2006-11-23 03:57:21 |
Scratch that last remark, working fine now - |
|
|
|
|
Posted : 2008-01-31 06:45:23 |
Tom Wrote: "As far as Linked tables are concerned, most MS Access developers use the "LinkODBC" sub procedure modules for their connections as they are compiled and are far more stable."
I think I like the idea of "more stable." Does this make it more efficent or faster?
I tried this and, unfortunately, the module crashes (gets stuk) when I try to run it. It gets hung up on the very first statements:
Public Sub LinkODBC() <---THIS IS HILIGHTED IN YELLOW Dim tdfAccess As TableDef <---THIS IS HILIGHTED IN BLUE
Error = "Compile Error: User defined type not defined"
Any idea why I can't get this to run?
I am trying to improve the stability, efficiency of my Access 2000 reports pulling data from Quickbooks 8.0 with QODBC 8.0. I have written under other threads because of my slow performance.
Does it really matter? Will it do something for me or should I quit worrying about it?
Please advise.
Tim
|
|
|
|
|
Posted : 2008-01-31 08:19:15 |
Ok... I turned on Microsoft DAO 3.6 Object Library and my previous error went away. I did this on advice of a Microsoft help library.
Now I get an error which says "Runtime Error 3000" with an unspecified error message.
It crashes on the following line.
Set dbsODBC = OpenDatabase("", False, False, strConnect)
Any suggestions?
Tim |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-01-31 08:47:44 |
Sorry this procedure is for MS Access 2003 or 2007 only. |
|
|
|
|