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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> List Of Tables, Access 2016    
 
   
payfast8898
post Nov 2 2018, 11:16 AM
Post#1



Posts: 377
Joined: 23-April 15
From: NY


How can I get a list of tables from a designated path which is stored in a field to appear in a list box.

Making my own linker and I have a couple base paths stored in one table.

so when I choose what table I'm want to link from I want only the tables in that stored path to show up in the dropdown.

any help or steer me to one this is already done would be helpful, I have found some table linkers but none that do this I have to type everything in manually leaving for mistakes.

Thanks.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
theDBguy
post Nov 2 2018, 11:27 AM
Post#2


UA Moderator
Posts: 77,341
Joined: 19-June 07
From: SunnySandyEggo


Does the list of tables in the path change? If not, or very rarely, maybe it is simpler to also store the name of the tables in your path table (or a child table), so you don't have to use code to loop through the list of tables from the path.

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
payfast8898
post Nov 2 2018, 11:52 AM
Post#3



Posts: 377
Joined: 23-April 15
From: NY


yes for different projects, I want to use it in all of my projects not just one and to start typing them all in will take for ever, I do have one I used but didn't work for passwords so the one I'm working on works great for that now.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
RJD
post Nov 2 2018, 11:53 AM
Post#4


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


Edit: See my much simpler and more complete solution below, directly getting the table names from external dbs.

Hi: While I agree with theDBguy that the list should not change much, if at all, and that could be created ahead of time.

However if that is not the case and you do want to go get the tables list, and you have access to the subject dbs, then there is a possible way to do what you describe. I don't have a full-up demo for you, but here is something to think about...

Create a macro in the target dbs to run a routine to output a CSV file (named with the db name) containing the db name and tables.

The export should call a query like this ...

CODE
SELECT CStr([Application].[CurrentProject].[Name]) AS DBName, MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6)) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

This lists the db and tables.

From the receiving db, open the target db with a macro command line addition. This will create the CSV file with the table list by db name.
Import the CSV table list and use it as needed.

Just a process idea ... and you may be able to streamline the process some ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
kfield7
post Nov 2 2018, 01:26 PM
Post#5



Posts: 1,032
Joined: 12-November 03
From: Iowa Lot


You might just point the mysysobjects query at the target database, instead of having the target db running a macro:

CODE
SELECT MsysObjects.Name
FROM MsysObjects IN [YourDBpathHere]
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;


Works for me anyway. Joe's inclusion of the database name is an excellent idea too.
This post has been edited by kfield7: Nov 2 2018, 01:30 PM
Go to the top of the page
 
payfast8898
post Nov 2 2018, 01:54 PM
Post#6



Posts: 377
Joined: 23-April 15
From: NY


ok kfield7 this is what I have before but I don't know how to incorporate the password for this in the code something to do with ";pwd=123" for example

down below is what I'm using to import with a password and the pwd works I'm sure it is something simple I'm just not getting it


Dim dbs As DAO.Database
Set dbs = DBEngine.OpenDatabase([tablepath], False, False, ";pwd=123")
DoCmd.TransferDatabase acLink, "Microsoft Access", [tablepath], acTable, "Accounts", "Accounts"
dbs.Close
Set dbs = Nothing

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
kfield7
post Nov 2 2018, 02:52 PM
Post#7



Posts: 1,032
Joined: 12-November 03
From: Iowa Lot


I haven't done this with passwords, but try

;pwd="123"

instead of ";pwd=123"
Go to the top of the page
 
payfast8898
post Nov 2 2018, 06:33 PM
Post#8



Posts: 377
Joined: 23-April 15
From: NY


I get a "characters found after end of SQL statement" using that

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
RJD
post Nov 2 2018, 08:09 PM
Post#9


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


Okay, here should be a much simpler way to get the table list for another Access db (than I suggested before), even if it has a password. You will not have to have anything extra in the other db and the other db will not have to be opened. I tested this with a PW protected db and it seems to work fine, without indicating the PW (since you are not opening the other dbs) (Checking issues with pw protection again.). You can run this for any db for which you want a list of tables.

SELECT "ReportName.accdb" AS DBName, MSysObjects.Name AS table_name
FROM MSysObjects IN 'C:\temp\ReportName.accdb'
WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6)) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

Just correct the db file name and the path. This can be done literally or using VBA against a table list of external dbs. Or you can UNION several queries against different dbs and produce a single list of dbs and their tables.

This was tested against a single PW protected db but should work against a multi-db set. (Just tested the UNION approach with two db files and it works like a charm, one PW file and one no PW file.)

Note: Some issues with the password protected db. I will check this again... Sorry.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
payfast8898
post Nov 2 2018, 09:26 PM
Post#10



Posts: 377
Joined: 23-April 15
From: NY


I'm still getting invalid password

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
RJD
post Nov 2 2018, 10:24 PM
Post#11


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


Hmmm ... I will check this again.

Could you post the query you are using that is not giving you the correct results? That may perhaps tell us something.

HTH
Joe

Edit: Changed post - checking code and files for pw issues.

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Nov 3 2018, 04:03 AM
Post#12


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


Okay, looks like this might be fixed now. Look at the syntax below (found HERE after several searches - says for A97, but works with A2010 as well and probably your A2016). Replace the file name, path and pw with your own. This syntax is tested and seems to work okay.

SELECT "FileName.accdb" AS DBName, MSysObjects.Name AS table_name
FROM MSysObjects IN '' ';database=C:\temp\FileName.accdb;PWD=123'
WHERE (((Left([Name],1))<>"~") AND ((Left([Name],4))<>"MSys") AND ((MSysObjects.Type) In (1,4,6)) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

That's two single quotes after the IN ... then space then the rest. Odd, but works in my test.

Sorry about the pw stumble. It worried me, so started very early today to fix it. I just knew there had to be a way ... "You just keep pushing ..."

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
payfast8898
post Nov 3 2018, 08:32 AM
Post#13



Posts: 377
Joined: 23-April 15
From: NY


Works awesome Joe thank you so much for all your time you put into it! I got spoiled early being able to add my tables with a click so I really do appreciate it.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
RJD
post Nov 3 2018, 09:35 AM
Post#14


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


You are very welcome. While getting the table names from another db was rather straightforward, the real breakthrough for me was finding the correct way to include the password. I never would have guessed the proper syntax, and found it only in the one ref cited. I think this approach is useful for other data-gathering as well.

Continued success with your project ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
payfast8898
post Nov 3 2018, 12:25 PM
Post#15



Posts: 377
Joined: 23-April 15
From: NY


I agree especially having to use a more secure back end's is very helpful. I added to this code back in 2016 for the linking tables with a password if anyone else is reading this and needs it. http://www.UtterAccess.com/forum/index.php...1328573&hl=
Thanks again Joe

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
RJD
post Nov 3 2018, 03:32 PM
Post#16


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
isladogs
post Nov 3 2018, 04:29 PM
Post#17


UtterAccess VIP
Posts: 2,109
Joined: 4-June 18
From: Somerset, UK


Hi Joe

Whilst your query to list tables in an external database will work, it can be simplified further

1. The pair of single quotes around the database path / password are superfluous and can be omitted
However I do that section slightly differently
CODE
IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb]


2. None of the system tables will ever have Flags=0. They can have various values including 2, 10, 262154 & -2147483648 BUT NEVER 0
The only possible exception is a system table created by the user such as USysRibbons which is normally hidden (Flags=8) but users could make that visible

So this part can be omitted : AND ((Left([Name],4))<>'MSys')

3. The filter Left([Name],1))<>'~' is only needed if you are trying to get a list of queries (Type=5) where it can be used to exclude so called temp queries (Flags=3) used as row sources for forms/reports & their controls.

So this should give exactly the same results as your query
CODE
SELECT 'FileName.accdb' AS DBName, MSysObjects.Name AS TableName
           FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb]
           WHERE (((MSysObjects.Type) IN (1,4,6)) AND ((MSysObjects.Flags) = 0)))
           ORDER BY MSysObjects.Name;

What that will not list are any tables hidden by the user (Flags=8). So if those are also required, this will do the job:
CODE
SELECT 'FileName.accdb' AS DBName, MSysObjects.Name AS TableName
           FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb]
           WHERE (((MSysObjects.Type) IN (1,4,6)) AND ((MSysObjects.Flags) IN (0,8)))
           ORDER BY MSysObjects.Name;

BTW this also works for ACCDE files
HTH
This post has been edited by isladogs: Nov 3 2018, 04:43 PM

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
RJD
post Nov 3 2018, 06:45 PM
Post#18


UtterAccess VIP
Posts: 10,189
Joined: 25-October 10
From: Gulf South USA


Hi isladogs: Thanks for that. When the OP asked the question, I was curious and started looking for solutions. The final suggested solution was a merging of a few solutions I found searching for specific requirements, plus a desire to crack the password issue. I had never had to use the password part, so this was new to me (I had already done some things without the password part) and I thought I should have that in my toolbox. The password solution was strictly from one found ref after a good bit of looking around and testing.

You have given us a more complete look at how to deal with this requirement - and is a very useful addition.

Thanks for that, and I am sure the OP thanks you as well.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
payfast8898
post Nov 3 2018, 08:39 PM
Post#19



Posts: 377
Joined: 23-April 15
From: NY


Yes thank you isladogs very helpful. there is one to many right parenthesis on the third line this is the fixed version and it also works great. Appreciate both your help and time.

SELECT 'FileName.accdb' AS DBName, MSysObjects.Name AS TableName
FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb]
WHERE (((MSysObjects.Type) IN (1,4,6)) AND ((MSysObjects.Flags) = 0))
ORDER BY MSysObjects.Name;

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
payfast8898
post Nov 4 2018, 12:31 AM
Post#20



Posts: 377
Joined: 23-April 15
From: NY


ok one more question I can't seem to get to work, how do I use variables in place of the file path and password in the FROM MSysObjects IN '' [MS Access;PWD=xyz;DATABASE=C:\temp\FileName.accdb] statement?
I uploaded a screen shot of what fields I would be drawing the data from and I can get the select statement ok just having trouble figuring out the other. thanks for any help.
Attached File(s)
Attached File  dblinker.JPG ( 41.03K )Number of downloads: 8
 

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    28th January 2020 - 03:03 AM