UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Can I Automate Connect To All Tables In Odbc Source?, Access 2016    
 
   
basson
post Aug 27 2019, 05:04 PM
Post#1



Posts: 877
Joined: 26-March 02
From: St.Louis, MO, USA


Hi,
I have a new ODBC Teradata data set at the office and need to link in several hundred tables. I can select them all but the problem is connecting them is rather slow and every few tables it stops and pops up another box that says, "select unique Record Identifier" which basically stops it from linking any more tables until I click tht "OK" box for it to move to the next table. This could take hours so I was wondering if anyone has any sort of code that would allow me to loop through all the tables and auto-click the OK box when it pops up so I could just let it run overnight and have it connect to all the tables unattended.
Go to the top of the page
 
Phil_cattivocara...
post Aug 28 2019, 03:47 AM
Post#2



Posts: 360
Joined: 2-April 18



I cannot help you with extracting all tables' name from your external source (I do not know Teradata) but, starting from a list of all tables built... in any possible way, you could create a local table in Access with that list, looping through each value, and adding a tabledef object to tabledefs collection. Here are some lines of code I used to add linked tables in A2013 to dbf files (don't make me remember A2013 and dbf, please)
CODE
Dim db As DAO.Database
      Dim tblDefs As DAO.TableDefs
      Dim tblDef As TableDef
      Dim rst As DAO.Recordset
      
      Set db = DBEngine(0)(0)
      Set tblDefs = db.TableDefs
      Set tblDef = New TableDef
      
      Set rst = db.OpenRecordset("yourtablename", dbOpenDynaset, dbReadOnly)
      If rst.RecordCount <> 0 Then
      'here starts a loop through local table where you have your external tables' list
          Do while not rst.EOF
      
              With tblDef
                  .Name = strTableName
                  .Connect = strConnect
                  .SourceTableName = strSourceTbl
              End With
              tblDefs.Append tblDef
              tblDefs.Refresh
      
          Next
      
      End If

I suggest to create with GUI a linked table, set a tabledef object to that and read all its properties, this is necessary to know what you need to write in .Connect property.I hope that using code you will have an error rising instead of the "OK" box, so you can handle it.It's everything to test and try, I never did it before.
This post has been edited by Phil_cattivocarattere: Aug 28 2019, 03:47 AM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
basson
post Aug 28 2019, 08:15 AM
Post#3



Posts: 877
Joined: 26-March 02
From: St.Louis, MO, USA


Thanks for the suggestion. I'll see if we can figure out something from that.
Go to the top of the page
 
Phil_cattivocara...
post Aug 28 2019, 09:36 AM
Post#4



Posts: 360
Joined: 2-April 18



Little mistake. This line
CODE
Set rst = db.OpenRecordset("yourtablename", dbOpenDynaset, dbReadOnly)

has to become
CODE
Set rst = db.OpenRecordset("yourtablename", dbOpenTable, dbReadOnly)

otherwise RecordCount property could not be realiable.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
basson
post Sep 12 2019, 05:00 PM
Post#5



Posts: 877
Joined: 26-March 02
From: St.Louis, MO, USA


Thanks but IT cannot give me a list of tables. I can see them when I click External data -> odbc, select my driver, then the "link table" box pops up with a list of tables. So right now I can select them all but then almost every one takes 20-30 sec or more to add each table to the DB and then most pop up the additional select the pk type of box...so manually it could take days to link them all. There has to be a different way...When I ask IT they all have attitudes tha tMS Access is an outdated slow tool that no one should have! So not much help from them.

Go to the top of the page
 
isladogs
post Sep 12 2019, 05:18 PM
Post#6


UtterAccess VIP
Posts: 1,680
Joined: 4-June 18
From: Somerset, UK


That's a fairly typical IT response.
I recommend using DSN-less connections but that also requires a list of tables.
Can you obtain those using code before linking tables?

--------------------
Go to the top of the page
 
AlbertKallal
post Sep 12 2019, 07:06 PM
Post#7


UtterAccess VIP
Posts: 2,845
Joined: 12-April 07
From: Edmonton, Alberta Canada


That does not make sense. There is some big un-known detail here.

Linking of an access table does not, and should not take 20 seconds.

This suggests some other issue here that should be resolved.

And furthermore, the linking of the tables is a “one time” occurrence anyway.

So, it not clear what kind of network connection to the database you have, but it suggests that some kind of VPN or internet connection is in-between you, and the server.

However, as noted, such linking is usually a one-time deal and once you are linked, then a re-link should be a rather rare event.

And by creating your own links, then you not see a prompt. In fact the selecting of the PK value is in general NOT required if you only read only.

There are “lots” of table re-linking example code – even a good number on UA

As for a list of tables?

Well nothing is stopping you from grabbing such a list. However, before you do this, is the list REALLY large, and do you actually need all of the gazillion tables linked?

Perhaps you want to run say just a few SQL queries against this data, and you don’t need a gazillion tables linked anyway?

The reason I ask and point out the above? Is perhaps you link one pass-through query to the database, and such a query NOW allows you to query ANY table on the server without having to create a new table link. Thus any table, any join between tables etc. can be created in that ONE query, and you can use any and all tables in the whole database in that PT query. So they are often great, since the query can use any of the say 300 tables that exist in the database, and you don’t have to create links to all of the tables for this to work.

The OTHER really nice feature of a PT query?

You can use it to get a list of tables from the system.

Eg:


QUOTE
SELECT * FROM dbc.tables WHERE tablekind = 'T' and databasename='SALES


You can double check the Teradata docs, but I believe the above is the correct syntax to get all tables in your database.

So the above will return the list of tables for you, and your VBA loop can thus use the results of this query to create + link all of the tables.

So, first is create a PT query. Then paste in the above SQL (modify for your needs), and run it. The result will be your table list.

From above, you can then process above list along with some re-link code.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 06:27 AM