Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-31-2019, 11:03 AM   #1
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 559
Thanks: 20
Thanked 102 Times in 91 Posts
sxschech is on a distinguished road
Query a Table in another database Syntax Variations

This is more about opinions rather than a specific question.

When I happen to decide to view data in another Access file, I sometimes use and have recommended to others the following syntax:

Code:
SELECT *
FROM tblTableName IN 'C:\Users\Documents\AccessFiles\AnotherFile.accdb';
Yesterday while searching the web on an unrelated topic, after I was finished, decided to click on some of the other articles (such as this one) https://www.databasejournal.com/feat...s--Part-II.htm and came upon a different syntax to achieve the same result:
Code:
SELECT *
FROM [C:\Users\Documents\AccessFiles\AnotherFile.accdb].tblTableName;
Seems like it might be better when constructing it as a query string since it doesn't use quote marks and there is no confusion about IN which is also used in queries for criteria instead of OR. Other than that are they functionally equivalent or is the original method preferred as that is how Access constructs it if using the graphical interface?

sxschech is offline   Reply With Quote
Old 05-31-2019, 11:13 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query a Table in another database Syntax Variations

Hi. Basically, when you connect to a table, Access needs to know where it is and how to connect to it. This is the function of a connection string. Those two methods you showed were just different ways of passing the connection string to Access, so it know where to find the table you're trying to query. So, yes, I believe they are functionally equivalent. There are probably other ways to do the same thing besides the one you posted.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-02-2019, 12:25 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,573
Thanks: 13
Thanked 1,461 Times in 1,392 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query a Table in another database Syntax Variations

I have seen this syntax but have never had a need to implement it. I use linked tables and that means that I can use saved querydefs. If I need to point to a different BE, I relink the tables.

Is there some reason that you prefer to do this rather than use linked tables and querydefs which don't have to be changed when the BE changes?

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 06-02-2019, 01:12 PM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 722
Thanks: 3
Thanked 148 Times in 142 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Query a Table in another database Syntax Variations

Quote:
This is more about opinions
Great! Then I can't be wrong.

Can't think of a reason why either of these is preferable to linking BE tables. Can you pass a BE password using this string? If not, 1 down vote.

If network paths change (anyone who has worked long enough in a large company environment knows this happens) would I rather batch re-link BE tables or edit every sql statement where this technique is used? 1 down vote.

And then have to release an updated FE version? 1 down vote.
Can't think of an up vote reason.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-02-2019, 06:43 PM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,947
Thanks: 80
Thanked 1,567 Times in 1,455 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query a Table in another database Syntax Variations

My question is whether the "other table in another file" is more of an ad-hoc or one-off type of operation, or whether you are more likely to visit this same table but just not very often. If this is a one-off case, I'm not sure that it matters. If this is a "repeated but infrequent" case then I would go for a linked table.

I agree that it might be easier to build the required string in your second example because of no need for quotes. However, to be honest, that is a minuscule reason to choose one vs. another.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-02-2019, 07:48 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,048
Thanks: 36
Thanked 724 Times in 707 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query a Table in another database Syntax Variations

Quote:
Originally Posted by Micron View Post
Can you pass a BE password using this string? If not, 1 down vote.
I believe you can, but I could be wrong.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-02-2019, 08:23 PM   #7
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,959
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Query a Table in another database Syntax Variations

I agree, to read data just set links to tables. However, I have a process to export data to a db and email it to another office on a periodic basis. The IN approach is what I happened to find at the time. Example of code:

Code:
Public Sub ConstructionExtract()
'exports data to ConstructionExtract Access file
'copies file to zip folder
'opens Outlook and attaches file to msg and sends
Dim strZip As String
Dim strExtract As String
strZip = gstrBasePath & "Editing\ConstructionExtract.zip"
strExtract = gstrBasePath & "Editing\ConstructionExtract.accdb"
'delete records from ConstructionExtract tables
CurrentDb.Execute "DELETE FROM Bituminous IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM BituminousMD IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Concrete IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Emulsion IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM PGAsphalt IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SoilsAgg IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SampleInfo IN '" & strExtract & "'"
'insert records into ConstructionExtract tables
CurrentDb.Execute "INSERT INTO Bituminous IN '" & strExtract & "' SELECT * FROM ConstructionBIT;"
CurrentDb.Execute "INSERT INTO BituminousMD IN '" & strExtract & "' SELECT * FROM ConstructionBMD;"
CurrentDb.Execute "INSERT INTO Concrete IN '" & strExtract & "' SELECT * FROM ConstructionCONC;"
CurrentDb.Execute "INSERT INTO Emulsion IN '" & strExtract & "' SELECT * FROM ConstructionEMUL;"
CurrentDb.Execute "INSERT INTO PGAsphalt IN '" & strExtract & "' SELECT * FROM ConstructionPG;"
CurrentDb.Execute "INSERT INTO SoilsAgg IN '" & strExtract & "' SELECT * FROM ConstructionSA;"
CurrentDb.Execute "INSERT INTO SampleInfo IN '" & strExtract & "' SELECT * FROM ConstructionSampleInfo;"
'create empty zip folder
'found this on web, no idea what the Print line does but if it isn't there, this won't work
Open strZip For Output As #1
Print #1, "PK" & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
'copy file into zip folder
Dim objApp As Object
Set objApp = CreateObject("Shell.Application")
'variable for source file doesn't seem to work in this line
'also double parens not in original example code but won't work without
objApp.NameSpace((strZip)).CopyHere gstrBasePath & "Editing\ConstructionExtract.accdb"
'open Outlook, attach zip folder, send e-mail
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email here"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "Central Materials Laboratory Data"
    .HTMLBody = "Construction data extract: " & Now
    .Attachments.add (strZip)
    .DeleteAfterSubmit = True 'to not retain in sent folder
    .Display
    ''.Send
End With
'delete zip folder
Kill strZip
CurrentDb.Execute "UPDATE Updates SET ConstructionExtract=#" & Date & "#"
End Sub


__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 06-02-2019 at 08:30 PM.
June7 is offline   Reply With Quote
Reply

Tags
access sql , external database , query , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Find Variations of a string trevor2524 Forms 3 02-24-2016 12:27 PM
[SOLVED] Integrity Query - Finding variations kit_sune Queries 3 10-20-2015 09:35 AM
Check for variations query? 510sx Queries 7 05-04-2010 10:57 AM
Syntax for updating table in another database from current db. muttsylove Queries 1 05-10-2006 11:37 PM
Query Syntax for SQL Table jamie57 Queries 2 08-26-2002 08:42 AM




All times are GMT -8. The time now is 03:45 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World