System Admin Tasked with converting ADP to ACCDB

[jasieltego]

Hi Everyone,

I have signed up to this forum to hopefully receive some guidance on a huge task , that's how it feels to me.
I recently have been poking around Macros and learning some VBA here and there. I'm a System Administrator, that somehow ends up doing a little bit of everything else.
We currently have a database we use that was created in 2010. I'm tasked with converting it to ACCDB.
I done some google searching and I have a starting point. I have indeed gotten a hold of the ADP file, and I have imported all the all the Modules, Reports and forms from the ADP. There were no tables in that ADP. Now that I have this, I'm not exactly sure what the next step is.

Is it getting a copy of the SQL Database?
Or is it converting the tables to local tables? However I don't see any tables at all in the ADP objects Imported.
Any Clarification would be very helpful.

Thanks

 

[arnelgp]

there were no tables because it is using ADO recordset.
with that you need to setup an ODBC connection to your sql server.
and you need to troubleshoot all Connection string that ADO is using
so you can connect to your database/tables.

there are lots on the Web on this topic, suggest you google them.

 

[jasieltego]

there were no tables because it is using ADO recordset.
with that you need to setup an ODBC connection to your sql server.
and you need to troubleshoot all Connection string that ADO is using
so you can connect to your database/tables.

Can I just make a backup of that DB and host it locally on another PC?
I don't want to mess up the live DB and it's connection.

If so, I can get this step completed fairly easily. I can then report back

 

[arnelgp]

Can I just make a backup

ofcourse you need to make a backup of the db and keep the backup in a safe place in case
anything goes wrong.

 

[kentgorrell]

Users should all be using their own copy of the Front End, so they should all currently have an ADP. You should be working on an accdb that you will give to everyone else once you've got it working and tested. So in the mean time other users should not be affected.

The MAIN difference is that you will need to create links to the SQL Server tables. DSN less is ideal but using a DSN (ODBC Datasource) in the short term may be easier. So no, you won't convert them to local tables. They stay in SQL Server. You are only changing the way you connect to them.

I'm no expert on ADPs, I saw the writing on the wall twenty years ago and avoided them. But I think your main objective is to convert your form and report Record Sources to use the linked tables.

The main point I'm trying to make here is that other users should not be affected by your changes until you are ready to roll out a new version.

My advise would be to find a consultant who has done an ADP to accdb conversion at least once.

 

[theDBguy]

Hi. Welcome to AWF!

You may also have to convert some of the stored procedures into local functions or create code to execute them properly from Access. For example, you may have to use code to pass parameters in a pass through query.

 

[GPGeorge]

Hi Everyone,

I have signed up to this forum to hopefully receive some guidance on a huge task , that's how it feels to me.
I recently have been poking around Macros and learning some VBA here and there. I'm a System Administrator, that somehow ends up doing a little bit of everything else.
We currently have a database we use that was created in 2010. I'm tasked with converting it to ACCDB.
I done some google searching and I have a starting point. I have indeed gotten a hold of the ADP file, and I have imported all the all the Modules, Reports and forms from the ADP. There were no tables in that ADP. Now that I have this, I'm not exactly sure what the next step is.

Is it getting a copy of the SQL Database?
Or is it converting the tables to local tables? However I don't see any tables at all in the ADP objects Imported.
Any Clarification would be very helpful.

Thanks

The tables exist. They are in a database in a SQL Server instance. There are no local Access tables.

If you install SSMS, and if you have appropriate permissions, you can connect to that SQL Server instance where you'll find the database with the tables. Actually, you should do that anyway as part of the conversion to an Access Front End.

 

[jasieltego]

thanks everyone.
I have all the SQL scripts which I believe are the tables. They are named xxxXX-table.sql
Is there a way from those scripts I can convert those tables to local tables?

 

[Pat Hartman]

I've never worked with an ADP but I've looked at one. Everything is done with code and recorsets If you import all the objects into an .accdb, what happens? Can you go to the vba window and try to compile the code? what happens? Since everything is done with VBA for an .adp, can you just leave it alone? Have you tried to open the converted database? Did it work?

It is significantly more work to create an .adp than an .accdb with linked tables but you can create an .accdb that has no linked tables and does everything with DAO recordsets the same way an .adp does so I'm not sure what the difference would be. Of course if Access can't import the forms/reports and the code behind them, that's a different problem.

To run the .accdb, you would need to know what the startup form or macro is and also whatever references are needed so you need to do two things.
1. open any code module in the .adp.
2. go to Tools/References an write down the names of all the references that have checkmarks.
3. open any code module in the .accdb
4. go to Tools/References and compare the references to the list from the .adp and make sure they match.

From the ribbon on the .adp, to to File/Options/Current Database and find the name of the Display Form. If this is empty, the db is started by the AutoExecMacro so you don't have to do anything. If there is a name there, you have to enter the same name in the .accdb

If the app complies without errors, try to run it and see what happens. I'm assuming that you have an account in the production database so the app can log into the Server using your credentials.

 

[theDBguy]

thanks everyone.
I have all the SQL scripts which I believe are the tables. They are named xxxXX-table.sql
Is there a way from those scripts I can convert those tables to local tables?

It depends. You could give it a try. Some of them might work, but some of them you may have to modify due to incompatible data types and other reasons.

 

[GPGeorge]

thanks everyone.
I have all the SQL scripts which I believe are the tables. They are named xxxXX-table.sql
Is there a way from those scripts I can convert those tables to local tables?

I am not sure we have all the facts on the table.

"We currently have a database we use that was created in 2010. I'm tasked with converting it to ACCDB."

I took that statement to mean the database is in use NOW. Is it not?

If so, the tables DO exist in a SQL Server instance. That's what an ADP is.

So, why are you abandoning the SQL Server back end? Why not keep it? You can continue to use the data in it, can you not?

 

[jasieltego]

I am not sure we have all the facts on the table.

"We currently have a database we use that was created in 2010. I'm tasked with converting it to ACCDB."

I took that statement to mean the database is in use NOW. Is it not?

If so, the tables DO exist in a SQL Server instance. That's what an ADP is.

So, why are you abandoning the SQL Server back end? Why not keep it? You can continue to use the data in it, can you not?

no one has access to that SQL instance.
The man previously in charge of this left and no one knows anything about it.

I was able to track down some database files, but there's currently 40-50 of them in a directory along with log files.
I'm assuming, I can just get one of those MDF files and create a new instance and restore it.
I never done this, I'm just thinking aloud.

 

[GPGeorge]

no one has access to that SQL instance.
The man previously in charge of this left and no one knows anything about it.

I was able to track down some database files, but there's currently 40-50 of them in a directory along with log files.
I'm assuming, I can just get one of those MDF files and create a new instance and restore it.
I never done this, I'm just thinking aloud.

Yes, yes you can. Do any of those file names correlate to the name of the ADP? Start with it.

Do you have SSMS installed?

 

[jasieltego]

Yes, yes you can. Do any of those file names correlate to the name of the ADP? Start with it.

Do you have SSMS installed?

About 10 of the have the same name with a different number appended at the end. It must be one of those ten.
I'll open them up one by one to check.

I do have SSMS.
I have a local installation of it, and backups.

I think I found the right MDF , Because when I try to copy and paste it over to a new location it says, it can't because its in use by another program.
IS there any options to copy it over without stopping the DB Service on that box?
I don't think so, I might need to bring it offline for a small period of time

 

[GPGeorge]

My guess would be that the numbered MDFs are backups of the original, made over time. Which one has the most recent file modified date? I would NOT try to copy and paste one of them.
I think, however, you can safely bring it offline inasmuch as it's only being used by the non-working ADP.

That said, can you not open it in SSMS? Do you have the right Login and Password? Can you retrieve them from the ADP?

 

[jasieltego]

My guess would be that the numbered MDFs are backups of the original, made over time. Which one has the most recent file modified date? I would NOT try to copy and paste one of them.
I think, however, you can safely bring it offline inasmuch as it's only being used by the non-working ADP.

That said, can you not open it in SSMS? Do you have the right Login and Password? Can you retrieve them from the ADP?

I can't login to it over SSMS, neither can anyone else. The only person who had access to it left.
As a matter of fact this instance resides on a Virtualized server , that only that user had access too.
Unfortunately, no one ever had access to it. SA password is not the default either.

That is why I was thinking copying the mdf to another VM. Then restoring it as a new instance. Why do you not recommend I copy and MDF backup and paste it to another location, and open it on a new instance?

I don't know how I would be able to retrieve a login and password from an ADP.

 

[Pat Hartman]

So, this application has been working all along but the programmer left for whatever reason and now no one can use the application???? Maybe you should call the police and file a complaint for sabotage by an employee or a contractor.

 

[GPGeorge]

I can't login to it over SSMS, neither can anyone else. The only person who had access to it left.
As a matter of fact this instance resides on a Virtualized server , that only that user had access too.
Unfortunately, no one ever had access to it. SA password is not the default either.

That is why I was thinking copying the mdf to another VM. Then restoring it as a new instance. Why do you not recommend I copy and MDF backup and paste it to another location, and open it on a new instance?

I don't know how I would be able to retrieve a login and password from an ADP.

Yeah, sorry I was not quite clear on those details.

To be honest, this is more of a detective task than a straight up Access/SQL Server task.

I don't think you even CAN copy the MDF if it's online and SQL Server thinks it's in use. There may be a way, but that's well beyond my experience.

I would be more hopeful if you can find a .BAK file of the same name, which would be a SQL Server created backup, and that should be usable to restore to a new database. But again, without a login and password for it, that may be a bridge too far as well.

If the existing data is critical to your operations, you might want to call in a SQL Server consultant if you want to retrieve the data from the original SS database.

SQL Server is frequently recommended when data security is an issue, and this is a good example of why. Getting to that existing data could be difficult.


And that brings us back to the scripts you previously mentioned. If they are complete and up-to-date and accurate, you could create a new SQL Server database and run those scripts in it to recreate the raw, empty tables. Existing data, though, is still locked away from you

 

[arnelgp]

you can open the "code" from the db and get the servername/password from
the connection string?

 

[sonic8]

I can't login to it over SSMS, neither can anyone else. The only person who had access to it left.
As a matter of fact this instance resides on a Virtualized server , that only that user had access too.
Unfortunately, no one ever had access to it. SA password is not the default either.

Did you try to extract the user/passwort credentials from the ADP file?
Try the following in the VBA Immediate Window:

? Application.CurrentProject.Connection.ConnectionString
? Application.CurrentProject.AccessConnection.ConnectionString
? Application.CurrentProject.BaseConnectionString

With the level of professionalism shown in the handling of this application, there is a good chance that this user account will have admin permissions on the SQL Server.

If the above fails...

Is the computer in a Windows Domain? Then the domain admin account will most likely also have administrator access to the SQL Server running on that machine.

If computer is not in a Windows Domain or the above does not work:

1. Clone/copy the VM.
2. Reset the local Windows admin account password (there are instructions by Microsoft on how to do this)
3. The local Windows Administrator with the reset password will also have access to SQL Server.

Finally, you can decided whether you will use the cloned VM just to make an accessible backup of all the data and put it on a different server or if you will replace the production VM with your new clone with the now know password.

Do I need to add, that it is f***ing big business risk to run a production SQL Server that no administrator has access to? This should be addressed with utmost urgency.