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: 158
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: 21,151
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: 322
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

--------------------
Go to the top of the page
 
Doug Galayda
post Mar 21 2017, 09:17 AM
Post#4



Posts: 158
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: 158
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: 322
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.

--------------------
Go to the top of the page
 
Doug Galayda
post Mar 22 2017, 01:26 PM
Post#7



Posts: 158
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
 
Doug Galayda
post Mar 29 2017, 11:23 AM
Post#8



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


●We had a adp that would not work with Office 365,
So I imported the adp forms and reports into a new access 2010
accdb Databse
●And then also made a new odbc read only connection
●And then linked to all the SQL tables
●I am in the process of try to rewrite the queries on the access side
●I read where in order to get a migrate an adp to accdb to work
This was the way to go. And I wanted to see after I made the new
.accdb whether I could open it in the office 365’s access, which is the next step:
The result was that I can open up the app I created with Access 2010 in
office 365, BUT when I try to open the linked table I get the message:

“The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from modules, forms and reports. You should back up your database before attempting to open
The database and delete the VBA project”

So I read where this could be a 64-bit versus 32-bit issue so I checked each:
And they say once they have been compile with one or the other and match
there is no more error.

●Access 2010 I recreated the .adp with—it is 32-bit
●Office 365 Access 2016--it is 32-bit

Since they do match 32 to 32-bit, do you have an idea on why I am getting this error?
I imported all the components into a new database, so this message concerns me as it is a show stopper
and I don't see how to identify the .adp to delete.
Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2017 - 03:43 PM