tags:

views:

114

answers:

1
+1  Q: 

Migrating database ms access 2003 to 2007

I'm very new to Access and I don't have any experience. At work, I was ask to research about migration of MS Access 2003 databases to 2007.

I have read on the Internet that usually if you open .mdb files in Access 2007 it will normally work. But in my case I'm getting an error:

Action Failed

Now I'm not sure if I'm getting the correct file here.

Here is the structure of the file of the database. The database name is: Energy.

Folder Energy:
Access2003
Energy.app
System.MDW
Energy (it's a link)
Energy.ini

I made a copy of my Energy.app and then renamed it to Energy.mdb. And this file Energy.mdb I opened in Access 2007. Then I got the error. How can I find the .mdb file? Did I do the right thing?

I also read that I have to make a copy of the front-end or back end of my database. I'm not sure where is the front end and back end now.

Is it in the SQL Server? Thanks for your help in advance.

Additional Question

I tried to follow this one from the website:

Method 2: Move the database to a trusted location

To do this, follow these steps:

  1. Determine trusted locations to which you can move the database. To do this, follow these steps:

  2. Click the Microsoft Office Button, and then click Access Options. Click Trust Center, and then click Trust Center Settings under Microsoft Office Access Trust Center.

  3. Click Trusted Locations, and then use one of the following procedures: Note the paths of the trusted locations that are listed. Add a new trusted location. To do this, click Add new location, and then specify the path of the location that you want to add.

  4. Move the Access 2007 database to the trusted location that you want.

But it seems it didn't work. The error I'm getting is after I deactivate the macro under option is this:

Error in Connection
SQLState '28000'
SQLServer Error 18452
[Microsoft][ODBC SQL Server Driver][SQl Server] Login Failed for user '(null)' Reason: not associated with a trusted SQL server connection

But I already added a new folder to the trusted location, as mentioned on the KB

+2  A: 

Have a look at this

Are you allowingtrusted mode?

astander  2009-09-22 14:01:47
hi astander, thanks for the link. Do I have to do this in ms access 2007? Or in SQL Server?
tintincute  2009-09-22 14:18:50
the link says that you need to do this in ms access 2007. Are you moving from access to sql server?
astander  2009-09-22 14:32:26
First I would like to open the .mdb files to access 2007 see if it works. If it works then we have to migrate from 2003 to 2007.
tintincute  2009-09-22 14:40:36
At the top, below the ribbon, it give you a warning"Security Worning - Certain content in the database has been disabled - Options..." do you see this?
astander  2009-09-22 15:03:19
yes I see this. Actually I tried to open this several times and also activate but still I'm getting an error message.
tintincute  2009-09-22 15:07:28
Do you have any macros that you run on open, any code that you run that might be failing?
astander  2009-09-22 15:41:33
the old database has macros active. i tried to activate the macros in ms access 2007 it seems still to fail.
tintincute  2009-09-22 18:54:45
i think the next step would be to check what those macros are doing, or trying to debug that. can you show me what you are trying to do in those macros?
astander  2009-09-22 19:42:25
you mean in ms access 2003 macro? or in ms access 2007? in ms access 2007, i clicked on the option and activate the macro from here.and every time i opened it i have to activate it.
tintincute  2009-09-23 06:39:02
the file that you are trying to convert, (i assume it is originally from access 2003). Does it have any macros or code running on startup? I think if so, this might be the code that is causing the error.
astander  2009-09-23 06:53:52
yes that's right. it's originally from access 2003 and it has macros running on start up. this file from access 2003, i'm trying to open it to ms access 2007. because the goal is to convert the ms access 2003 to ms access 2007.
tintincute  2009-09-23 07:01:50
Yes, i understood that. So now my guess would be that what ever is running in that macro is what is failing. If you can give me a clue as to what you are trying to do in the macro, amybe i can then investigate that.
astander  2009-09-23 07:33:41
ok. you mean in the macro ms access 2003? I'm not really sure what to look in macro. could you give me a hint what to look? thanks. this project is done by somebody and i have to spell all the works done here.i'm thankful for your ideas and help
tintincute  2009-09-23 07:40:59
Typically the macro calls a function in VBA code. This will then execute steps, such as set settings, hide forms, update values etc.In access 2003. Under the Macros tab (should be below reports,pages), do you have any macros there, and if so, what are they called?
astander  2009-09-23 08:10:49
tintincute  2009-09-23 08:23:20
OK, thats good. The Autoexec is the one that runs when you open the db normally (without shift). Now the next question, what is the autoexec doing, is it calling a vba function in one of the modules?
astander  2009-09-23 08:46:41
hmm how will i know that? what function shall i look? do i have to open the VBA code?
tintincute  2009-09-23 09:15:58
Under the Macros, select Autoexec, right click and open in design view. Tell me what you see inside the macro, what is it doin?
astander  2009-09-23 09:26:27
it opens like an excel file with a name at the top: Autoexec: Macro and under Action: ExecuteCode und under the Functions Name : InitApplication(). that's all
tintincute  2009-09-23 09:36:33
OK, thats good. Now, in the VBA code, in a Module you will find this InitApplication() function. Look for that edit your question to copy that code in if you are allowed to, and leave a comment again for me to find so that i can have a look at that code. I think your issue is in that code...
astander  2009-09-23 09:46:23
i just opened any Module and already copied the code. You can see it now in my edited question. I have a little background on C# but not on VB
tintincute  2009-09-23 10:18:51
Sorry, was out for the day, will have alook at it as soon as possible. Adriaan
astander  2009-09-23 20:57:04
ok. thanks Adriann.
tintincute  2009-09-24 06:42:20
MK, the code came out a bit funny in the post. Tell me, do you know how to debug the code? When you open it in access 2007 (using shift, to avoid it running the marco), you need to go the the vba code where the function InitApplication is. On the line that says "Application.Echo 1, conStart" press F9, this should insert a break point. Now you can go back to the macros section, double click and debug the code. See if you can find the line where the code fails. Other than that, goto (in vba code) DEBUG->Compile. Does it give errors?
astander  2009-09-24 22:21:10
tintincute  2009-09-28 19:23:33
Im sorry, my german is a bit bad X-), can you translate what the messages are saying?
astander  2009-09-28 19:26:59
no problem:) That means: "Please wait while Microsoft Office professional Plus 2007 is being configured by Windows. Required information are being collected" Sorry about that, the German translation is really very bad. At the moment, I can't think how to translate it well in English. The Blue Window it just says: "Status Configuration" "Microsoft Office Professional is being configured"...
tintincute  2009-09-28 19:48:22
hi astander, you mentioned in your prev comment, that I should find the line "Application.Echo 1" I already found it and already inserted the breakpoint. And you said I go back to the macros section again. In my macros section I have Autoexec and mcrTool,which one should I click?
tintincute  2009-09-29 07:49:06
Double click the AutoExec macro, which should run the code
astander  2009-09-29 08:35:39
This is really strange. I tried to put the break point where the "Application.Echo 1, conStart" and double click the AutoExec macro, nothing happened. I go back to Debug >Compile it's grayed out- hmmm, do you have any idea?
tintincute  2009-09-29 11:41:08
i am getting a bit stumped. if at all possible, is there somewhere you can post the db file, so i can have a look at it local?
astander  2009-09-29 20:33:43
yes sure I think I can, hmmm can i send it to your email?
tintincute  2009-09-30 11:25:01
that should be fine [email protected] This is a free site, so the file cannot be to big, sorry...
astander  2009-09-30 11:38:30
ok thanks Adriaan,will send you later. thanks
tintincute  2009-09-30 13:18:53
i sent it already did you get it? thanks
tintincute  2009-10-01 06:39:53
Yes, i did get it, but at home i only have 2003, which helps us nothing X-). At work i have 2007, so i can check there.
astander  2009-10-01 07:14:00
i see ok, thanks again for your time. let me know what results you get:)
tintincute  2009-10-01 07:30:21
hi Adriann, were you able to check the db I sent?
tintincute  2009-10-05 06:58:28

related questions

Backup SQL Schema Only?
Sql to query a dbs scheme
Timer-based event triggers
Sql query, count and group by
Paging SQL Server 2005 Results
SQL 2005 For XML Explicit - Need help formatting
How do I use T-SQL Group By
What all do I need to escape when sending a (My)SQL query?
Split String in SQL
Datatable vs Dataset
ASP.NET MVC "CRUD" Database Sample
Convert HashBytes to VarChar
Best Book for a new Database Developer
What is the best way to avoid SQL injection attacks?
What language do you use for Postgresql triggers and stored procedures?
What is the best way to handle multiple permission types?
How do I index a database field
Swap unique indexed column values in database.
cx_Oracle - what is the best way to iterate over a result set?
cx_Oracle - How do I access Oracle from Python?
Is there a version control system for database structure changes?
How to export data from SQL Server 2005 to MySQL
ASP.NET Site Maps
Decoding T-SQL CAST in C#/VB.net
Flat File Databases in PHP