Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.
COURSE of the MONTH
9 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Could you point how to programatically change an Access database using C#?
Posted on 2016-09-09
Hi Experts
Could you point how to programatically change an Access database using C#?
I'm involved in develop an application that I expect, from time to time, for this to work out, changes in an Access database must have to be done.
The app is distributed and installed at user's machine. So I need to programatically apply the changes when needed. When a new release is published and installed, and then at the .exe very first run, it automatically find the necessary changes to be made at Access database and do it, making the app runs perfectly.
Thanks in advance.
Could you point how to programatically change an Access database using C#?
I'm involved in develop an application that I expect, from time to time, for this to work out, changes in an Access database must have to be done.
The app is distributed and installed at user's machine. So I need to programatically apply the changes when needed. When a new release is published and installed, and then at the .exe very first run, it automatically find the necessary changes to be made at Access database and do it, making the app runs perfectly.
Thanks in advance.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
3
- +3
13 Comments
Active today
Well how easy is this it depends on the kind of you manipulation you need to perform.If it is on table/query level then you easily "send" CRUD commands to Access to do what you want.
In forms/reports it seems there is a bit more complicated although it there are available "tools" ( Access.Controls, VBIDE.VBComponent,VBIDE.VB
In forms/reports it seems there is a bit more complicated although it there are available "tools" ( Access.Controls, VBIDE.VBComponent,VBIDE.VB
Active today
The regular approach to this is:
Split your Access application into a "app/client part" and a "backend/server part".
Note that the backend can be either an access database, SQL server db or some other database.
Now distribute the client part to your users (preferably as a accde file, so they can't mess with the code), and when you need to do updates, you simple distribute a new accde file to everyone.
A Version check on startup can ensure that everyone is using the latest version.
Split your Access application into a "app/client part" and a "backend/server part".
Note that the backend can be either an access database, SQL server db or some other database.
Now distribute the client part to your users (preferably as a accde file, so they can't mess with the code), and when you need to do updates, you simple distribute a new accde file to everyone.
A Version check on startup can ensure that everyone is using the latest version.
Active today
You have two options:
1) Updating an existing database. Depending on the kind of changes you need either Access-SQL and/or ADOX.
Caveat: applying changes to an Access database so that the result is an exact copy is under some circumstances not possible, cause not all internals are public accessible.
2) Always deploy the actual version of your database and import the data from the old version.
Caveat: this can be a multistep operation.
And as always: Create backups before applying updates.
1) Updating an existing database. Depending on the kind of changes you need either Access-SQL and/or ADOX.
Caveat: applying changes to an Access database so that the result is an exact copy is under some circumstances not possible, cause not all internals are public accessible.
2) Always deploy the actual version of your database and import the data from the old version.
Caveat: this can be a multistep operation.
And as always: Create backups before applying updates.
Active today
Hi for all and thank you for your replies.
ADOX is something similar to MS-SQLServer DDL isn't it?
I guess an approach could be an Access app responsible for all needed table creation, tables' structure changes and so on, no C# codes used for this, am I right?
ADOX is something similar to MS-SQLServer DDL isn't it?
I guess an approach could be an Access app responsible for all needed table creation, tables' structure changes and so on, no C# codes used for this, am I right?
Active today
ADOX is equivalent to SQL Server Management Objects (SMO), but it is only an abstraction on the OLE DB layer.
In the beginning I used Access SQL DDL scripts to adjust the structures and Access SQL DML to adjust the data.
But in the end I often distributed a new database and let the installer or application on the first run import the data from the old database. So I only need Access SQL DML scripts.
And in the I even used an "update" Access database to host the migration queries and necessary procedures. Then the update process linked the tables from the old database and the new database. Then the queries and when needed macros where executed.
Both ways a viable.
The first can lead to much shorter and faster scripts in many cases. But you need to script out every change, which can be a bit of a problem, when you don't do it always on the fly. And it needs serious testing of each step.
The second approach has a simpler logic. Thus fewer pit falls, cause not everything can be scripted as DDL. Also it is easier to test and you can write your migration scripts in the end.
In the beginning I used Access SQL DDL scripts to adjust the structures and Access SQL DML to adjust the data.
But in the end I often distributed a new database and let the installer or application on the first run import the data from the old database. So I only need Access SQL DML scripts.
And in the I even used an "update" Access database to host the migration queries and necessary procedures. Then the update process linked the tables from the old database and the new database. Then the queries and when needed macros where executed.
Both ways a viable.
The first can lead to much shorter and faster scripts in many cases. But you need to script out every change, which can be a bit of a problem, when you don't do it always on the fly. And it needs serious testing of each step.
The second approach has a simpler logic. Thus fewer pit falls, cause not everything can be scripted as DDL. Also it is easier to test and you can write your migration scripts in the end.
Active today
Back to Ander's point:
Is the database split into FE and BE? If not, why not? All shared applications should be split as well as any application you create for another person. I occasionally create apps for my own use that are not split but it would be very poor practice for me to create monolithic apps for the use of others - shared or not shared because it would make updating the application objects very difficult.
Splitting the app allows you to simply replace the FE with an updated version when you want to push out a code update. When you make schema changes, you can use DAO or ADO to make the changes after first backing up the client's data file.
For my apps that are distributed to the public, I create an Access database with VBA and DAO to update the BE or run scripts should the BE be SQL Server. But I would NEVER attempt to poke changes into a FE. In fact, if you distribute the FE as an .accde, you would not be able to update forms/reports/code anyway.
Is the database split into FE and BE? If not, why not? All shared applications should be split as well as any application you create for another person. I occasionally create apps for my own use that are not split but it would be very poor practice for me to create monolithic apps for the use of others - shared or not shared because it would make updating the application objects very difficult.
Splitting the app allows you to simply replace the FE with an updated version when you want to push out a code update. When you make schema changes, you can use DAO or ADO to make the changes after first backing up the client's data file.
For my apps that are distributed to the public, I create an Access database with VBA and DAO to update the BE or run scripts should the BE be SQL Server. But I would NEVER attempt to poke changes into a FE. In fact, if you distribute the FE as an .accde, you would not be able to update forms/reports/code anyway.
Active today
At first sight It looks something really new for me, just a moment to reflect a little.
Active today
After a more appurated reading to everyone posts.
The suggestion is to mantain an "app/client part (FE - frontend)" and a "backend/server part (BE)" of Access database, isn't it?
I'm certainly misconcepting concepts but what I catch is:
If the client app (C#) identifies an database version change it automatically asks for download the newer version of database from internet server.
After download, a mechanism(*) first backups the current database and starts to migrate the current data to the newer database just downloaded. Then the FE app could start now.
(*)VBA and DAO to update the BE or run scripts should the BE be SQL Server.
Could you check my interpretation?
The suggestion is to mantain an "app/client part (FE - frontend)" and a "backend/server part (BE)" of Access database, isn't it?
I'm certainly misconcepting concepts but what I catch is:
If the client app (C#) identifies an database version change it automatically asks for download the newer version of database from internet server.
After download, a mechanism(*) first backups the current database and starts to migrate the current data to the newer database just downloaded. Then the FE app could start now.
(*)VBA and DAO to update the BE or run scripts should the BE be SQL Server.
Could you check my interpretation?
Active today
Ignore the BE/FE part. This is about plain Access-Access applications. Where the UI is also in Access.
Where does SQL Server comes into play now?
Where does SQL Server comes into play now?
Active today
It looks like you have a C# app that is connecting to an Access database. If so, then the repeated suggestions to split that Access database are unnecessary - you don't split a data-only database.
If your C# application needs to make structural changes to the Access database, DAO is generally the preferred method for doing so. Jet/ACE does support a subset of DDL commands, so you could certainly use those if all you're doing is adding columns and such.
But you don't need to split your Access database if all it does is host your data.
If your C# application needs to make structural changes to the Access database, DAO is generally the preferred method for doing so. Jet/ACE does support a subset of DDL commands, so you could certainly use those if all you're doing is adding columns and such.
But you don't need to split your Access database if all it does is host your data.
Active today
@ste5an
I mentioned "SQL Server" only to reinforce my interpretation on what @PatHartman had posted.
It doesn't came into play, sorry. "Ignore the BE/FE part. This is about plain Access-Access applications. Where the UI is also in Access" cleared my overall comprehension.
@Scott
Your considerattions are very closer on what I expect to do since the start. I did it similar with other systems I distributed before, using another tecnology.
In this case it's just a matter of construct a class to deal with the changes needed, basically: to create a newer table or add new columns to existing tables. Using " Jet/ACE subset of DDL commands"as you mentioned.
Everytime the C# system starts it checks if it's necessary to actualize Access database.
I guess @ste5an and @Scott approaches are aligned to what I need to do.
Now it's up to me to find "Jet/ACE subset of DDL commands" to fit my purposes. Maybe you could possibly post an small example on how to do this.
I mentioned "SQL Server" only to reinforce my interpretation on what @PatHartman had posted.
It doesn't came into play, sorry. "Ignore the BE/FE part. This is about plain Access-Access applications. Where the UI is also in Access" cleared my overall comprehension.
@Scott
Your considerattions are very closer on what I expect to do since the start. I did it similar with other systems I distributed before, using another tecnology.
In this case it's just a matter of construct a class to deal with the changes needed, basically: to create a newer table or add new columns to existing tables. Using " Jet/ACE subset of DDL commands"as you mentioned.
Everytime the C# system starts it checks if it's necessary to actualize Access database.
I guess @ste5an and @Scott approaches are aligned to what I need to do.
Now it's up to me to find "Jet/ACE subset of DDL commands" to fit my purposes. Maybe you could possibly post an small example on how to do this.
Active today
In truth, it looks easy an easy task, I tryed that way, with success.
public void CriaTabela() // Creates a table
{
try
{
string strTemp = " [KEY] Text, [VALUE] Text ";
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = clsConnections.CN;
myCommand.CommandText = "CREATE TABLE table1(" + strTemp + ")";
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
public void CriaColuna() // Creates a column
{
try
{
OleDbCommand myCommand = new OleDbCommand();
myCommand.Connection = clsConnections.CN;
myCommand.CommandText = "ALTER TABLE table1 ADD COLUMN SignIn DATETIME";
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
}
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents. The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.