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
> Migration Of 2003.adp To 2010.accdb In Prep For Office 365, Access 2016    
 
   
Doug Galayda
post Mar 29 2017, 02:15 PM
Post#1



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


Migration of 2003.adp to 2010.accdb in prep for office 365
●Using SqlServer 2008
●We had a Access .adp that will not work with Office 365,
So I imported the adp’s 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 trying to rewrite the queries on the access side(it has a SQL server backend)

●I read where in order to migrate an adp to accdb This was the way to go.

What prompted the current error that is generated in office 365/access is
that 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 progressive step, but,,,,
The result was that I can open up the app I created with Access 2010(using parts of the .adp) in
office 365, BUT when I try to open the linked table I get the below error 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”

I googled this and 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

I did check the borrowed laptop's access 2010(32 bit) and my regular machine which is now office 365 file/account/ is 32-bit,,,, they are both 32-bit/32-bit,
I am checking on a SPI/RTM
So any suggestions on why I am getting this error?
or do you have any idea what to try next?
I imported all forms and reports to the new database and this message concerns me as it does not make sense and it is a real show stopper if I cannot get linked tables to show data.

Also I am reading the below article, to see if conflicting SP1/RTM compiles applies to me:
I have not ran anything yet in the access 2010.accdb version as all the queries are messed up(I am just resourcing tables to reports)
also how do you compile a access program?
•Does below KB article apply to your situation?
http://support.microsoft.com/kb/2533794

The following tables provide a summary of which compiled databases will work with which version of Access 2010. The tables assume that you are opening a 32-bit MDE, ACCDE, or ADE file in the 32-bit version of Microsoft Access or that you are opening a 64-bit MDE, ACCDE, or ADE file in the 64-bit version of Access. You cannot open a 32-bit MDE, ACCDE, or ADE file in 64-bit Access, and you cannot open a 64-bit MDE, ACCDE, or ADE file in 32-bit Access.

An MDE, ACCDE, or ADE file that was created in Access 2010 RTM
Kind of file Access 2010 RTM Access 2010 SP1
32-bit MDE, ACCDE, or ADE Works Works
64-bit MDE, ACCDE, or ADE Works Error occurs

An MDE, ACCDE, or ADE file that was created in Access 2010 SP1
Kind of file Access 2010 RTM Access 2010 SP1
32-bit MDE, ACCDE, or ADE Error occurs Works
64-bit MDE, ACCDE, or ADE Error occurs Works
This post has been edited by Doug Galayda: Mar 29 2017, 02:26 PM
Go to the top of the page
 
DanielPineault
post Mar 29 2017, 03:02 PM
Post#2


UtterAccess VIP
Posts: 5,077
Joined: 30-June 11



CODE
in prep for office 365

Do you mean in prep of Access Web Apps? If so, check out https://www.devhut.net/2017/03/27/steer-cle...plications-awa/

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
Doug Galayda
post Mar 29 2017, 03:54 PM
Post#3



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


No sorry not for access web apps,
Thanks for your suggestion though.
Appreciate it.
This post has been edited by Doug Galayda: Mar 29 2017, 04:38 PM
Go to the top of the page
 
GroverParkGeorge
post Mar 29 2017, 10:07 PM
Post#4


UA Admin
Posts: 30,191
Joined: 20-June 02
From: Newcastle, WA


I think that a bit of clarification on terminology and concepts might help.

There is no "Office 365" version of Access.

The version you have from an Office 365 subscription is going to be Office 2016, which includes Access 2016. It is essentially the same 2016 version of Access as you'd get by purchasing it as a "boxed" version, although the different methods of installation does involve some differences in the way they behave. But for all important purposes they are same.

Now, the problem you are seeing may or may not be related to the version, but whatever it is, you need to be clear on this point.

I doubt very much that you would have the 64 bit version of Office installed. The default is to install the 32 bit version and you have to go out of your way to install the 64-bit version. Here's how you would know for sure, though.


Attached File  2017_03_29_17_43_58.jpg ( 210.94K )Number of downloads: 5


So, let's not go astray on that path, unless, of course, you actually did manage to install the 64 bit version somehow.

The far more likely issue is that there is some problem in the VBA due to an incompatibility. That can well happen, as you read, from different bitnesses, but it's not likely here.

You can also check references in the VBA. That's another problem that can lead to the error you saw.

If you see a reference here marked "Missing:" that'll be the problem.


Attached File(s)
Attached File  2017_03_29_20_01_57.jpg ( 112.44K )Number of downloads: 1
 

--------------------
Go to the top of the page
 
LPurvis
post Mar 30 2017, 07:40 AM
Post#5


UtterAccess Editor
Posts: 16,051
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Just adding on the back of this here...

>> I imported all forms and reports to the new database and this message concerns me as it does not make sense and it is a real show stopper if I cannot get linked tables to show data.
It does also sound potentially corruption-like. Are you saying that you've carried out the above action since creating the ACCDB? (i.e. re-imported into a new ACCDB again from the first ACCDB?)
Can you access the VBA project in the VBA at all? I'd probably lean towards exporting to text and back into a new project.

>> I am in the process of trying to rewrite the queries on the access side(it has a SQL server backend)
Why's that?
If the "queries" are already in the SQL Server, the consuming them in your ACCDB makes sense too. It's not quite as trivial as it was in an ADP, but entirely possible.
Linking to views and executing SPs and using UDFs as required elsewhere.
Rewriting to Access queries is only likely to eat up time and cause poorer performance.

Cheers

--------------------
Go to the top of the page
 
Doug Galayda
post Mar 30 2017, 05:20 PM
Post#6



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


I did check
●my Access 2010 is 32-bit and
●my Access 2016 is 32-bit

Short recap:
1)We have a access .adp app connected to SQL server
2)I imported the forms, reports from the original.adp
To a new Access.accdb in access 2010
3) then I made a OBDC (read only)connection to it.
4) then I linked the tables (in access 2010)

I did notice that tables look different in the original .adp, image on left,
They have a trailing (dbo) [in access 2010]

I do know that when I linked the tables in the access 2010
It added on and extra dbo to the name.

Then I see an Extra dbo_ in the names of the ones I linked to in the new access.accdb [in access 2010]
When I hover over the table name in access 2010
I see dbo.Add_Info
dbo.Allstate as the table names.
But I think, have not proved that the queries interpret the table names as different
—THAT would Not allow me to open the tables in access 2016, would it?

I will check the available references Now.
This post has been edited by Doug Galayda: Mar 30 2017, 05:54 PM
Attached File(s)
Attached File  adp_versus_accdb_linked_tables_Image.zip ( 79.74K )Number of downloads: 1
 
Go to the top of the page
 
Doug Galayda
post Mar 30 2017, 05:36 PM
Post#7



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


Pervis,
I don’t know how to get the VBA code(SQL query SQL text) from the access 2010.adp to the access 2010.accdb by using VBA code.
I really don’t want to rebuild them, and if you say it is unnecessary, I believe you.
I thought that was the only way to get from the adp to the accdb.

--To open the queries up in designer
Copy them in SQL mode, then create a new query as the same name in the new accdb
And then paste it in. That is a lot of hassle, I have done 2 like that so far.
And there must be a easier way.
I will try your VBA query copy if you tell me how.
I can open up the original adp and the new accdb in access 2010 at the same time on the laptop.
I have to connect the LAN cable to access UtterAccess on my desktop pc.
and I have to have to switch the LAN cable to the laptop to access the adp and accdb on the network
I guess I should make copies of the adp and accdb on the laptop's C:\ drive.
Thanks!
Go to the top of the page
 
GroverParkGeorge
post Mar 30 2017, 05:54 PM
Post#8


UA Admin
Posts: 30,191
Joined: 20-June 02
From: Newcastle, WA


Given the time differences, I'm going to assume that Leigh isn't online at the moment and offer a couple of suggestions, but he'll be back to address the issues he brought up.

Re naming conventions for linked tables.

When you have tables in a SQL Server database, they are contained within a schema. Think of a schema as a container within a database. Here's an example.

Attached File  2017_03_30_15_44_58.jpg ( 51.63K )Number of downloads: 1


When you link to tables using odbc, Access retains the schema as part of the link name, although the underlying table is still called the same.

Here is an example.

Attached File  2017_03_30_15_49_50.jpg ( 10.12K )Number of downloads: 1


Both of these point to the SAME SQL Server table. This first one was linked, and then the name changed to make it a little less cumbersome to work with in Access. The second one was linked later, but not renamed.

I don't recall the naming convention in .adps, but I imagine they would be like the second instance of tblAssignedDeveloper in my screen shot? Again, it doesn't impact the underlying connection or table in SQL Server, only the way Access sees and uses it.





--------------------
Go to the top of the page
 
Doug Galayda
post Mar 30 2017, 07:17 PM
Post#9



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


Q1)====================================================
Are you saying that you've carried out the above action since creating the ACCDB? (i.e. re-imported into a new ACCDB again from the first ACCDB?)

Q1) Answer: I created a new accdb database (in access 2010)
Imported the reports and forms from the original .adp (in access 2010)
Then created a odbc (in access 2010)
Linked the tables in the accdb (in access 2010)

The queries do not appear in the accdb, like they do in the adp
And I don’t know how to get them to appear under queries.

I just open up the copy of the access 2010 accdb in access 2016 ( I have a backup)
I did not import anything twice into the accdb
I can open the access 2010 created accdb in the access 2016 but
**If I go to the linked tables and try to open them in access 2016
It gives me 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”

Q2)=====================================================
Can you access the VBA project in the VBA at all? I'd probably lean towards exporting to text and back into a new project.

Q2) Answer: I can access the adp project from access 2010 (on the laptop)
I can open the adp fine, and look at the queries
What I am saying is, I want them to display in the new access 2010.accdb as queries
And I don’t know how to get them into the object panel.
(That’s why I started copying out the SQL from the adp and pasting into a new query in the accdb)
Thank you, I appreciate your patience.
Go to the top of the page
 
Doug Galayda
post Mar 31 2017, 12:48 PM
Post#10



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


Friday/3/31/2017 12::30pm
I am googling to see if it I possible to export out of the old original .adp to text
and import it into the new 2010.accdb
Thanks
Go to the top of the page
 
LPurvis
post Mar 31 2017, 12:59 PM
Post#11


UtterAccess Editor
Posts: 16,051
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

Sorry, busy days.

>> ... Linked the tables in the accdb (in access 2010)
Yep, I got tall that. I just meant have you Re-imported them all into another new ACCDB. If corruption is sniffing around, that's a decent action. They are usually, in ascending order of desperation:
Compact & Repair.
Decompile.
Import into new ACCDB.
Export to text and import from text. (What it seems you're now doing.)

>> The queries do not appear in the accdb, like they do in the adp. And I don’t know how to get them to appear under queries.
Yes that's standard. They won't appear under queries, as those are local Access queries. However, when linking your tables, you can link to Views as well.
(Note, not SPs or UDFs, but those can be executed by other means.) You don't want to be recreating everything on the client side. Particularly not if you've already got those objects in your SQL Server DB.

>> If I go to the linked tables and try to open them in access 2016 It gives me 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”
Wait just from opening the linked table you get that message? In the Access UI, you double click one of the linked tables and that message appears?

I understand that your ADP is working. And you have a new ACCDB that you've reacted linked ODBC table definitions to the tables in SQL Server (and can add your views to that also).
The ADP should be old news, you've moved on from it. :-) When I was referring to a "project", it was the VBA project. i.e. if you can open the code window and examine your VBA procedures. (Do you have many of those, ones that accessed the data via recordsets etc?)

Cheers

--------------------
Go to the top of the page
 
Doug Galayda
post Mar 31 2017, 03:17 PM
Post#12



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


Thanks George:
I am going to have to trim off the dbo_ and the go through the code also
as per this link below
https://social.msdn.microsoft.com/Forums/of...forum=accessdev
• I was actually able to do this pretty easily by doing a couple of things:
1. Created a blank new Access DB (MyAccessDB_new_format.accdb)
2. Opened it and went to the External Data menu, then imported all the objects from the old Access file (MyAccessDB.adp)
3. Went to View code to see the VBA code and then added the reference to the ADODB library for the code.
4. Created a new ODBC connection on the PC to the local SQL Server database (called MySQLServerAppDB).
5. Deleted all the local tables that got imported in the Access db.
6. Went to the External Data menu, then added all the db objects as linked tables (All the tables now in the access db with the globe icon next to them) and renamed them in Access from default naming schema of "dbo_TableName" to just "TableName".
7. Tested it and was getting some weird dbo message, so looked at the VBA code and all the queries had schema qualification (i.e.: select * from dbo.table1), so I did a find replace and got rid of "dbo." (found 10 occurrences) and changed any form or report references in their property sheets with dbo in the name and removed "dbo."
This post has been edited by Doug Galayda: Mar 31 2017, 03:18 PM
Go to the top of the page
 
Doug Galayda
post Mar 31 2017, 03:35 PM
Post#13



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


Pervis,

1)Yes I imported the forms and the report to a new accdb
There was plenty of VBA code in the adp, I will now
check to make sure it is in the forms, and report’s (form’s driver)
query VBA code came along with it.

**I know you can export a query’s data by right click export/text but
Is it possible to export the query itself? As text or any other means?
What I am saying is that the queries were listed on the access adp
And nothing will work in the new .accdb wihout them.

2) If I single click or double click I cannot open the newly linked tables in access 2016,
a single or double click generates the message,
but I can readily open them in the access 2010 .accdb that I created from the .adp

Q:I have to ask is there any one that you have heard of that has successfully migrated a adp with a menu screen,
a application screen, reports and a lot of queries to a new accdb?
Q: Is this possible??
Thanks!
Go to the top of the page
 
LPurvis
post Apr 3 2017, 05:59 AM
Post#14


UtterAccess Editor
Posts: 16,051
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

The ACCDB works in an Access 2010 installation? Perhaps consider that the 2016 installation is faulty then. Does it run under 2016 on a different PC?
The links you've created, these aren't something you've attempted to import from the ADP, but you've created these links (using the External Data function in Access perhaps)?

>> Q:I have to ask is there any one that you have heard of that has successfully migrated a adp with a menu screen,a application screen, reports and a lot of queries to a new accdb?

I didn't make enough ADPs to ever consider migrating one. (Only one ever released AFAICR.)
But your forms and reports and code should migrate reasonably well (as mentioned, exporting to text is a solid option - how has that gone by the way?)
Your tables won't export of course. You need to created those separately.

Your queries as views can be linked as linked tables. Functions and SPs have to executed by other means.

But it should be possible to consume a lot of what you did in the ADP. Moving objects to the Access client side is definitely not want you want for the most part.

Cheers

--------------------
Go to the top of the page
 
Doug Galayda
post Apr 3 2017, 12:28 PM
Post#15



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


Thanks Pervis,
1)I am going to stay positive and get on the access 2010 and try to export the queries from the .adp to the .accdb in access 2010

I have been reading this Microsoft link:
https://support.office.com/en-us/article/Ex...b5-aa5319a428a6
"Export a database object to another Access database: Applies To: Access 2007"

"You can export a table, query, form, report, macro, or module from one Access database to another. When you export an object,
Access creates a copy of the object in the destination database"

2)
I did notice that all the VBA code was stripped off of the forms after I imported them from the .adp form the accdb (in access 2010)
--thanks for your support because there are ALOT of forms in this application, and I do not want to re-build each of their “form load” event triggers, etc by copy and pasting from one open access app to another!
Thanks again for you support.

3) Since all the forms and reports rely on queries nothing is working in the new accdb In access 2010. This is what I imported the forms and reports to from the original .adp.
**I can open the forms on the 2.nd click, but no queries, no data.
**I can open the table in access 2010.
Go to the top of the page
 
Doug Galayda
post Apr 3 2017, 03:30 PM
Post#16



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


To reply to your 5:59am 4/03/2017 post: All belowe done in Access 2010
1)The .accdb forms and reports DO NOT work at all, it has no queries,
and the queries supply the data to the forms and the reports.

1.5) I created a brand new .accdb in access 2010.
I will try to open a the .accdb in access 2010 on someone else's machine, and check it.

2) I did create those linked tables the proper way with a ODBC connection and can access them, open them up and see the linked data.
the odbc connection is solid and I can see the data in the tables. So I don't see how it is a "bad installation"
(not that I am defending my limitations)

3) Also: Someone on line claimed to have been able to export queries from a
.adp to a accdb, so I gave that a try: it failed. Heres why:
●In Access 2010: I attempted to export from a .adp to a .accdb: but that is not allowed.
●I had the original .adp open and on the first query I did a right click/
Access/ picked the “to” database (the new .accdb) in access 2010,
And tried to export the query,
●At first, it tried to put dbo. In front of the query name
dbo._Active _w/Action
●Then it gave me an error that “this type of name was not allowed”.
●so I trimmed off the dbo. and tried to export again.
And received the below message:
"Queries cannot be imported, exported or copied to a Access database files."
4)Next I will create views on the sqlServer based on the queries.
then link to views like tables to source the forms and reports.
5)Also NO VBA code came along for the imported forms,
--all VBA code was stripped from the forms and report form drivers that were imported into the new .accdb from the original .adp)
This means I will have to re-create each event trigger then copy and paste in the VBA code from the original .adp event trigger to get a form or report to function.
This post has been edited by Doug Galayda: Apr 3 2017, 04:04 PM
Go to the top of the page
 
bakersburg9
post May 19 2017, 03:09 PM
Post#17



Posts: 4,860
Joined: 2-November 04
From: Downey, CA


Doug,
It seems you haven't posted on this topic in over a week - were you able to accomplish what you wanted ? I'm curious, because I'm being considered for a contract job where they want to do something similar.

Steve
Go to the top of the page
 
Doug Galayda
post May 24 2017, 05:23 PM
Post#18



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


I am so sorry Steve I did not see your post,
A heads up--it has been tough from the word go.
The show stopper was the report queries were written in all custum
Code and the programmer handed off the where clause to the
Select in variables.
In Access 2013 when ran added parenthesis causing me to do a re-write.
I am just doing the reports now.
I hope you are good at interpreting SQL queries and converting them to
Access queries. Because I could not use the former queries.

1)You cannot import queries from the .adp to the .accdb
They are resident on SQL server(they are not available when you are doing the import, they are simply not available, true)
2)you have to copy and paste the VBA code from the adp frms to a intermediate document, then the copy and paste it into the 2013.accdb
Imported ones on the .accdb side.

3)It may also delete out the selects on the record source lines
In the imported subform’s properties.

4) Also you have add dob_ to all the table names where used on the
access .accdb side.

Good Luck, it challenging to me but can be done.

This system was one menu, 4 buttons driving 3 reports and 1 screen
Check and see if it has a lot of custom VBA code,
And mine had a SQL server back end, that caused the most hassle.

This post has been edited by Doug Galayda: May 24 2017, 05:33 PM
Go to the top of the page
 
bakersburg9
post May 24 2017, 11:20 PM
Post#19



Posts: 4,860
Joined: 2-November 04
From: Downey, CA


Doug,
Thanks so much ! I REALLY appreciate your reply - as I type this, my interview is in less than 15 hours - I took notes of everything you said, so I can at least speak knowledgeably - thanks !!!! cool.gif

Steve
Go to the top of the page
 
Doug Galayda
post May 25 2017, 07:37 PM
Post#20



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


Good luck you sound like a real go getter,
in worst case scenario,
you can just ask the user their requirements and redo the queries and reports.
thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th August 2017 - 07:30 PM