Using MS Access to query over several databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone please help me with this? We were using an MS access database to connect to SAP using an ODBC connection. We had to change the setup to use an MS access database instead of SAP. In other words, rather than using an ODBC to SAP I have created another MS Access database that held all of the required tables from SAP. I now have 2 MS Access databases and I am trying to do a query to one from the other.

My query looks like this:

Select <dbasename>.<tablename>.<columnname> from <dbasename>.<tablename>.<columnname> where <dbasename>.<tablename>.<columnname> = <somevalue>.

It requires me to input parameter values for any items in the select clause and the where clause even though I provide them in the where clause. My ODBC connection fails in the VB program I run because those other parameters are not supplied and I cannot even do a general query using this method.

Please help me!
 
I don't understand.

You can select from multiple linked tables, because they
appear to Access QBE as local resources. You can also
select from remote mdb files w/o linking, using this syntax ...

select * from C:\Local.mdb.tblIssues

--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast


Steve-o said:
Can anyone please help me with this? We were using an MS access database to connect to SAP using an ODBC connection.
Click to expand...
We had to change the setup to use an MS access database instead of SAP. In other words, rather than using an ODBC to
SAP I have created another MS Access database that held all of the required tables from SAP. I now have 2 MS Access
databases and I am trying to do a query to one from the other.
My query looks like this:

Select <dbasename>.<tablename>.<columnname> from <dbasename>.<tablename>.<columnname> where
Click to expand...
It requires me to input parameter values for any items in the select clause and the where clause even though I provide
Click to expand...
them in the where clause. My ODBC connection fails in the VB program I run because those other parameters are not
supplied and I cannot even do a general query using this method.
 
I may not understand your situation, but I'm wondering why you don't use a
single front-end, linked to both sets of back-end data?

Good luck

Jeff Boyce
<Access MVP>
 
That didn't work when I tried it.

You just give it a path and name and use a period to comma delimited table? No
quotes, apostrophes, brackets around anything?

like Select * from C:\MyDB.mdb.MyTable
??

You can select from multiple linked tables, because they
appear to Access QBE as local resources. You can also
select from remote mdb files w/o linking, using this syntax ...

select * from C:\Local.mdb.tblIssues
(e-mail address removed)
 
Correct. I NEVER need brackets ... because I never include spaces
in table or field names. I never include odd characters like question
marks, semicolons, tildes, pound signs or cyrillic alaphabet. For that
matter, I don't do any of the above when naming folders and files
either. I suppose that's why I have so few problems with this stuff.

If effect, Microsoft said to you: "Here's a gun. There's your foot.
Have fun boys."

If you are in the habbit of doing the above, then you HAVE to enclose
everything in square brackets. I ran this code on my machine before
posting it, just to be sure my syntax was correct. It worked.

select * from C:\MyDB.mdb.MyTable

Others will have to use this syntax (I tested this sql ... and it works):

select * from [C:\00 isn't windows great !\& isn't Access great too !.mdb].[? aren't tables great @]
 
Yes, yes, your idea was my first thought also.

To the Original Poster:

Why not just create some links to both data sources and then start writing
queries to your great happiness.
 
Back
Top