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
> Query From .adp To Accdb “getting Cannot Find File” Msg, Access 2010    
 
   
Doug Galayda
post Mar 20 2017, 05:59 PM
Post#1



Posts: 85
Joined: 19-May 10
From: Springfield, Illinois


A Copy and pasted SQL query is generating a cannot find file error:

We went to office 365 (with access 2013 on board) and I need to convert a .adp to a .accdb,
So I got a laptop with access 2010 on it, and using the Access 2010,
I imported the .adp's objects to a new .accdb database,
--it went fine until I hit the queries.
I have imported all the objects from the .adp, except for the queries.
I had to copy out the select's SQL from queries in design mode and paste them into the new .accdb.
But when I try to run the query, I am getting the message

“Could not find the file \\networkpath,,,,,\Doug.Galayda\dbo.mdb”

The first two queries are both based on a Local table.
Can you give me a clue to why the query is looking for a network path on a local table?
and generating this message?

Thanks!
Go to the top of the page
 
Doug Steele
post Mar 20 2017, 08:25 PM
Post#2


UtterAccess VIP
Posts: 20,950
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What's the SQL of the query that's raising that error message?

--------------------
Go to the top of the page
 
PhilS
post Mar 21 2017, 04:37 AM
Post#3



Posts: 294
Joined: 26-May 15
From: The middle of Germany


QUOTE
I have imported all the objects from the .adp, except for the queries.
I had to copy out the select's SQL from queries in design mode and paste them into the new .accdb.
But when I try to run the query, I am getting the message

“Could not find the file \\networkpath,,,,,\Doug.Galayda\dbo.mdb”

The first two queries are both based on a Local table.

You have got a misconception there. There are no local tables in an ADP-Project. None. Never. - All tables in an ADP are SQL-Server-Tables.
If you want to move an ADP to an AccDB, you need to link the SQL-Server-Tables to you AccDB-database via ODBC first. Then you can copy the SQL of the queries. You probably need to adjust the table names in the SQL statements. dbo.SomeTable becomes dbo_SomeTable or whatever local name you use for that table. It will certainly not contain a dot in it's name, because that is not allowed for Access object names.

Depending on the actual SQL of the queries, they might require much more adjustments or even a complete rewrite. The original queries in the ADP were written for the T-SQL-Dialect of SQL-Server and might not work at all inside an Access database.
This post has been edited by PhilS: Mar 21 2017, 04:39 AM

--------------------
Video tutorial for beginners - Build Strings and SQL-Strings in VBA
Go to the top of the page
 
Doug Galayda
post Mar 21 2017, 09:17 AM
Post#4



Posts: 85
Joined: 19-May 10
From: Springfield, Illinois


SELECT dbo.Action_Indef(DUE, COALESCE (WAIVER, DUE), pkLog_No)
AS [Action], LEFT(fkSiteID, 3)
AS FIPS, dbo.checkLog(pkLog_No)
AS indefsort, dbo.LaterDate(DUE, WAIVER)
AS Actionsort, pkLog_No AS Expr1, *
FROM [dbo].tblPermits
WHERE (DUE IS NOT NULL) AND (STATUS = 'A') AND (pkLog_No LIKE '%');

I also see three separate .dsn's out on the network with read only UID's
This post has been edited by Doug Galayda: Mar 21 2017, 09:19 AM
Go to the top of the page
 
Doug Galayda
post Mar 21 2017, 11:38 AM
Post#5



Posts: 85
Joined: 19-May 10
From: Springfield, Illinois


11:37am Tuesday/3/21/2017
Phil:
I will make a ReadOnly odbc Connection via the SqlServers readonly
UserId. Then I will have the dbo_ tables available
So when I paste the query/SQL code from the old .adp into the new .accdb
I will have the correctly named dbo_ table names available.
First I have to make a new odbc connection.
Will let you know as soon as I try it.
Thanks!
Go to the top of the page
 
PhilS
post Mar 22 2017, 03:39 AM
Post#6



Posts: 294
Joined: 26-May 15
From: The middle of Germany


After looking at the SQL of your query, it becomes apparent that a straight copy&paste with adjusted table names will not work.

The minor issues like the use if the T-SQL COALESCE function and the % wildcard for the LIKE comparison can be easily fixed.
But your query uses several user defined functions on SQL Sever (dbo.Action_Indef, dbo.checkLog, dbo.LaterDate). These functions are only available when executing the SQL directly on SQL Server.

To use this Query from Access, you either need to create a Pass-Through-Query in Access, or you need to create a view from this SQL on SQL Server and link that view into Access via ODBC.

--------------------
Video tutorial for beginners - Build Strings and SQL-Strings in VBA
Go to the top of the page
 
Doug Galayda
post Mar 22 2017, 01:26 PM
Post#7



Posts: 85
Joined: 19-May 10
From: Springfield, Illinois


Phil,

Tuesday, I did get all the tables linked in access 2010.

Today i can make a simple query on the new linked tables and it works.
And all the linked tables return data.

I will take another query that don't use user defined functions and
get that to work and then get one to work that serves a report

--Then get another to work that serves a simple form.
And try to go from there.
I Will let you know of progress and of problems.

And I will watch the suggested video.

Thanks for the advice!

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    27th March 2017 - 07:33 AM