VBA To Open Linked Table

[cheer]

There are 2 linked tables, TableA and TableB, from the SQL Server in my Access DB. These 2 tables are linked up through a query called, QueryA, for example.

I need to write a VBA codes in the MS Access by using the ADODB to open QueryA . How to write the VBA by using the ADODB ?

 

[CJ_London]

if your tables are linked, not sure why you need to use adodb

take a look at this link
http://www.databasedev.co.uk/ado_vs_dao.html

Note that adodb recordsets are different from dao and use different wildcards - e.g. they use % rather than *

It is OK to use '...Like 'abc%' but not '...like '%abc%' or '%abc'

 

[cheer]

1. The Access DB contains a lot of other ADODB codes inside. In order to standardize the programming method, I have to use ADODB to call up the QueryA

2. However, when I try to call the QueryA by using the ADODB, error message will be prompted out saying "connection xxxx failed!!!", properly due to the 2 linked table from the SQL Server through the ODBC connection

3. I try to plug in the codes recommended by Microsoft at here https://support.microsoft.com/en-us/kb/177594 by replacing the TableDefs with QueryDefs, however, it is helpless as the example only show how to open a linked table but not the QueryA from the 2 linked tables.

Any other advice ?

 

[CJ_London]

With what you have provided so far, all I can suggest is what I have already done

The implication is there is something wrong with your code or the values you are using so suggest post an example of code that works and the code you are using which fails

 

[cheer]

Appreciate if anyone can help in resolving the codes by inserting some codes to enhance or fix the existing codes

fyi, dim rsTemp as ADODB.Recordset

 

[CJ_London]

you are mixing up adodb and dao connection methods.

You said 'The Access DB contains a lot of other ADODB codes inside.' and I asked you to post some code that works which you have not done - but if you go to the link I provided, you will quickly see the difference

 

[cheer]

1) Which code made you say I am mixing ? I am fully use the ADODB from the beginning of the code till the end as shown on the above. I don't get you at all.

Fyi, before the 2 tables are migrated into SQL Server platform from the MS Access platform, I do not have problem in calling the V_Invoice_DO_Detail. However, problem occur when linked table is made inside the V_Invoice_DO_Detail.

2) I can't post the whole program. It is an ERP application, where the whole source code easily >30MB. What I can elaborate here in more details is, when I press the command button and fire the above codes, it stuck at the set rsTemp1 = cnnNewI3.Execute (strSQL) and error message is prompted

a) Before change : V_Invoice_DO_Detail link up 2 Access tables and everything goes perfect

b) After change : V_Invoice_DO_Detail link up 2 SQL Server linked tables and error message is prompted

 

[CJ_London]

you want an adodb connection but are questioning a dao method

for dao, which your second bit of code seems to be about
cnnnew13 has not been declared as a dao database
cnnnew13 has not been set to currentdb
you are trying to execute a select statement - you should use openrecordset - execute is for delete/append/update type queries
you have not declared rsTemp1 as any type of recordset

as far as your first bit of code is concerned, your connection string is for an access mdb (not accdb) yet you say 'There are 2 linked tables, TableA and TableB, from the SQL Server in my Access DB'

So basically, I do not understand what you are trying to do

 

[cheer]

1) you want an adodb connection but are questioning a dao method

Answer
I am not asking anything on DAO since my first post, you may have mis-interperted somwhere. Let come to the basic, i m asking how can I overcome the error message after replacing the 2 MS Access tables with the 2 SQL Server linked tables in a Access query call V_Invoice_DO_Detail.

The error message "ODBC-Connection DSN failed" is primary due to the connection to the 2 SQL Server linked tables during execution of the VBA codes. If someone can tell me how to connect the linked tables as and when through VBA coding, the problem will be solved permanently.

2) you have not declared rsTemp1 as any type of recordset

Answer
I have posted it at POST No 5: dim rsTemp as ADODB.Recordset

 

[WayneRyan]

cheer,

CJ, you have the patience of a saint.

I'm confused here too !

rsTemp1.Open strSQL, cnnNew13
Wayne

 

[cheer]

I have found the solution. After added the ODBC connection string into the query V_Invoice_DO_Detail, the problem resolved. Nothing to be changed or done in the VBA codes.

TQ everyone at here.