Migrating from Microsoft Access to My. SQLPaul Du. Boispaul@kitebird. Document revision: 1. Last update: 2. 00. Businesses that use Access eventually begin to struggle against. Now they have an alternative: Move data repositories.
My. SQL provides the features you need to follow this course. My. SQL. includes a business strength database server that runs under either. Windows, Linux, or Unix. My. SQL features Inno. DB, the table type.
By using My. SQL as a data management platform, you can continue. Access as a front end if you wish.
The SAP Community is the quickest way for users to solve problems, learn more about SAP solutions, and invent new ways to get things done.
But you can also exploit your data in other ways, for example. My. SQL to help you establish or strengthen your web presence. To do this, deploy a web server that handles requests from people. My. SQL using a web programming. A popular combination employs the Apache web server. Perl and PHP programming languages. Perl and PHP both.
My. SQL. The following article explores reasons to consider using My. SQL. instead of Access for data management, and provides guidelines. Access and into My.
SQL when. you're ready to make the switch. Microsoft Access is a popular data management application that. You can also use Access as a front end, that. In this case, Access. The My. SQL database system is one such storage manager; if you.
Enter your USERNAME: SID/EIN (Student/Employee ID Number) and we'll send you a link to change your password.
![Creating A Gui In Access Microsoft Program Creating A Gui In Access Microsoft Program](http://cdn.tidyform.com/Download/728/ms-access-crm-template-forms-download.png)
My. SQL Connector/ODBC driver (formerly known as My. ODBC). Access can make ODBC connections to My. SQL servers over the network. You can still use the contents of your tables through Access. My. SQL server. Access has its strengths, such as an easy to use interface. Access. also has its limitations- -it's generally used as a personal or. Access is not commonly used for databases hundreds of.
Because of its storage management. Access interface. Or you may even be considering a move away from. Access entirely. This article outlines some benefits that you. My. SQL to manage your data, and provides. Access tables. to My.
The Microsoft Access Call Log/Interaction Tracking Database is a full featured Microsoft Access Database Template that allows for viewing and creating Contact Call. The Enhanced Microsoft Access Task Management Database is a full featured Microsoft Access Database Template that allows for Scheduling and viewing Tasks in a.
SQL. The final section of the article lists links to locations. Use of My. SQL as a storage manager for Access offers several benefits. One is that you can use your information in additional ways when. Access. Other differences pertain more specifically. Access as the user. Deployment of information. When your information resides.
My. SQL, you're free to continue using it from Access if you. Any. kind of My. SQL client can use the information, not just Access.
This allows your data to be exploited more fully in more contexts. For example, other people can use the data. My. SQL client programs or from GUI- based applications. Your database also becomes more accessible over the Web. Access. now provides some capabilities for making a database available. Web, but if My. SQL manages the database, you have a wider.
My. SQL integrates easily with Web servers like. Apache through any of a number of languages, such as Perl, PHP. Python, Java, and Ruby. This allows you to provide a Web interface.
In addition. the interface can be accessed by browsers on many types of machines. All of these components can be obtained for free- -My.
SQL, Apache. and the languages just mentioned have been released as Open Source. You can also obtain them in packages that include support.
Multiple- user access. Although Access provides some data. It has. the feel of a single- user data manager designed for local use. My. SQL, on the other hand, easily handles many simultaneous users. It was designed from the ground up to run in a networked environment. Management of large databases. My. SQL can manage hundreds.
Care to try that with Access? Security. When Access tables are stored locally, anyone. Windows machine, launch Access, and gain access. It's possible to assign a database a password. When your tables are.
My. SQL, the My. SQL server manages security. Anyone attempting. My. SQL. Backup management. If you work in an organization that.
Access users, migrating data to My. SQL provides a. benefit for backups and data integrity.
With Access databases. My. SQL, they're all backed up using the regular. My. SQL backup procedures that already exist at your site.
If individual. Access users each store their data locally, backup can be more. While some sites. Local disk storage requirements. Local Access database.
My. SQL server where. This results in reduced local disk usage. And. should you wish to distribute a database, less information need. Of course, anyone you distribute the database to also. My. SQL server.)Cost. My. SQL can be obtained for free. Access cannot. Providing.
Web interface). can reduce your dependence on proprietary software and lower your. Hardware choices. My. SQL runs on several platforms; Access. If you want to use Access, your.
Should you wish to migrate from Access to My. SQL, you can do so. It's not without reason that Access. If you're such a user, you can continue to use the. Transfer locally stored Access. My. SQL, then set up links in the Access database that.
My. SQL server. This way you continue. Access interface (the tool with. My. SQL for data storage, management, and security. If you're less tied to the user interface, you can migrate completely. Access. Transfer your Access tables to My. SQL, then use. your information with tools intended for working with My.
SQL. In general, to migrate information from Access to My. SQL, you first. copy the contents of your tables from an Access database to the. My. SQL server. (To perform the operation of transferring the tables. My. SQL, you can choose from several methods, described below.).
If you plan to continue using Access for the interface to your. Delete the tables stored in your Access database. ODBC connection from Access to the My. SQL server. and recreate the tables as links to the My. SQL tables. (Naturally. If you don't plan to continue. Access, you need not create any links.
Some transfer methods require making an ODBC connection to the. My. SQL server. For this you can use My. SQL Connector/ODBC, the My.
SQL- specific. One approach to migrating data from Access to My. SQL is to use. the export feature provided by Access itself to write out the. Each file then can be loaded. My. SQL using a LOADDATA statement or the. Suppose you export a.
CSV. (comma separated values) format, and you want to import it into. My. SQL database named mydb. You can invoke the mysql program, then issue a LOADDATA statement to import the file like this.
C: \> mysql mydb. LOAD DATA LOCAL INFILE 'mytable. INTO TABLE mytable. FIELDS TERMINATED BY ',' ENCLOSED BY '"'.
LINES TERMINATED BY '\r\n'. Alternatively, use mysqlimport from the command line (type. C: \> mysqlimport - -local - -fields- terminated- by=,- -fields- enclosed- by='"'- -lines- terminated- by='\r\n'mydb mytable. If you need to provide connection parameters such as the hostname.
C: \> mysqlimport - -local - -fields- terminated- by=,- -fields- enclosed- by='"'- -lines- terminated- by='\r\n'- h some_host - p - u some_usermydb mytable. The advantage of this approach is that it requires no special. It can be used to produce data files even on. My. SQL support. (If you don't have the My.
SQL. client programs installed on your Access machine, create the data. My. SQL programs.
My. SQL from there.) The disadvantage. My. SQL tables must already exist before you can load. CREATETABLE statements yourself. For the example just shown. LOADDATA or mysqlimport. A second approach to data transfer is to use a converter that. Access table and produces from it one or more files containing.
SQL statements that create the table for you and load data into. Then you execute the intermediate SQL file or files using. Several free converters that work like.
Access. module: exportsql. Works with Access.
Access. 97, Access. Exports all tables. DROPTABLE. statements (in case you want to remove My. SQL tables created during.
CREATETABLE and INSERT statements. The files are written to. C: \TEMP directory. Exports all tables in a database into a file C: \TEMP\mysqldump. DROPTABLE, CREATETABLE. INSERT statements to drop any existing My.
SQL tables. and recreate them. Less sophisticated than exportsql. Access. 97 only. Exports selected tables to files in a directory. Writes a data file for each selected table. SQL script containing CREATETABLE. LOADDATA. statements for importing the data files into them. Near the beginning of the source code for each of these converters.
SQL and data files are converter- specific. Also, be sure to note any prerequisites that must be satisfied. These include the following.
Both exportsql. txt and access_to_mysql. C: \TEMP directory, so you must create.
C: \> mkdir C: \TEMP. Alternatively, you can modify the module source so that it writes.
Creating Active Directory accounts using Power. Shell. Provisioning new user accounts in Active Directory is part of the parcel of the daily job of administrators in enterprise environments. New hires need user accounts created for them before they can log on to their assigned domain- joined desktop or laptop computers. New employees can also suddenly show up in bulk when your company or organization makes an acquisition or merger with another business.
While single accounts can be created using GUI tools like Active Directory Users and Computers (ADUC) in older versions of Windows Server or using the Active Directory Administration Center (ADAC) in Windows Server 2. Windows Server 2. R2, it’s more common in enterprise environments to leverage the power of scripting to automate repetitive tasks like creating new user accounts.
Windows Power. Shell is an ideal vehicle for doing this, and the purpose of this article is to demonstrate some simple ways you can use Power. Shell to create new user accounts in Active Directory both singly and in bulk. The explanation and procedures included below are adapted from my book Training Guide: Installing and Configuring Windows Server 2.
R2 (Microsoft Press, 2. Also included near the end of this article are a couple of additional tips on this subject that I’ve gleaned from the larger IT pro community including the almost 1.
WServer. News weekly newsletter which you can subscribe to at http: //www. Managing user accounts with Windows Power. Shell. Creating and managing user accounts is a common Active Directory administration task. Windows Power. Shell provides considerable flexibility in how this can be done in Windows Server 2. Windows Server 2. R2. Typing Get- Command *ADUser at a Windows Power. Shell prompt shows there are four cmdlets for managing user accounts: New- ADUser – Creates a new Active Directory user.
Get- ADUser – Gets one or more Active Directory users so that you can perform some action with them. Set- ADUser – Modifies the properties of an existing Active Directory user. Remove- ADUser – Removes the specified user from Active Directory. Any administration of user accounts using Windows Power. Shell involves using one or more of these cmdlets. The following sections demonstrate some of the ways you can create new user accounts using the New- ADUser cmdlet. The approach you choose depends on the particular needs of your situation.
Example 1: Create a single new user account. To create a new user account for Phil Gibbins using pgibbins for the user’s SAM account name and [email protected] for the user’s UPN logon, you can use the New- ADUser cmdlet as follows: PS C: \> New- ADUser - Name “Phil Gibbins” - Given. Name Phil - Surname Gibbins `- Sam. Account. Name pgibbins - User. Principal. Name [email protected]Note that there is no output if the command runs successfully.
The resulting properties of the new user account when it is opened in ADAC are shown in Figure 1. Note that there are numerous other properties you could have specified when creating the account. Each of these additional properties has a parameter associated with it when using the New- ADUser cmdlet.
Figure 1: Create a new user account using the New- ADUser cmdlet. Note that if you try the preceding example, you’ll discover that the user account for Phil Gibbins is created in the Users container of the domain. To create a user account in a different location, you must specify the - Path parameter with this command. For example, to create this account in the location ou=Seattle Users OU,ou=Seattle OU OU,dc=corp,dc=contoso,dc=com in Active Directory, you could append - Path “ou=Seattle Users OU,ou=Seattle OU OU,dc=corp,dc=contoso,dc=com” to the command used in the preceding example. Example 2: Create a new user account and specify a password. To specify a password when you create the user account for Phil Gibbins, you can use the Read- Host cmdlet. With this cmdlet, you enter a password when you run the command, as shown by the highlighted code in the following example: PS C: \> New- ADUser - Name “Phil Gibbins” - Given.
Name Phil - Surname Gibbins `- Sam. Account. Name pgibbins - User.
Principal. Name [email protected] `- Account. Password (Read- Host - As. Secure. String “Account. Password”)Example 3: Create and enable a new user account. When you use the New- ADUser cmdlet to create a user account, the new account is disabled and cannot be enabled unless either of the following has occurred: A valid password has been set for the account.
The - Password. Not. Required parameter has been set to true. To create a user account for Phil Gibbins, specify a password, and enable the new account, you can use the following command: PS C: \> New- ADUser - Name “Phil Gibbins” - Given. Name Phil - Surname Gibbins `- Sam.
Account. Name pgibbins - User. Principal. Name [email protected] `- Account. Password (Read- Host - As. Secure. String “Account. Password”) `- Pass.
Thru | Enable- ADAccount. The - Pass. Thru parameter, which has been added to the New- ADUser command just shown, returns the newly created user account object so that it can be piped into the Enable- ADAccount cmdlet to enable the new account. Example 4: Bulk- create new user accounts.
A good example of how you can use Windows Power. Shell to automate a common Active Directory management task is the bulk creation of users. For example, you can combine the previous examples with the Import- Csv cmdlet, which enables you to read in data from a comma- separated values (CSV) file to create multiple user accounts in a single operation. To illustrate this, the file new- users. Name,Given. Name,Surname,Sam. Account. Name,User. Principal. Name. Arno Bost,Arno,Bost,abost,[email protected]Peter Fischer,Peter,Fischer,pfischer,[email protected]Manish Chopra,Manish,Chopra,mchopra,[email protected]The following command first reads the CSV file and pipes its contents into the New- ADUser cmdlet, then sets the password for each user account as Pa$$w.
PS C: \> Import- Csv C: \data\new- users. New- ADUser - Pass. Thru | `Set- ADAccount.
Password - Reset `- New. Password (Convert. To- Secure. String - As. Plain. Text ‘Pa$$w.
Force) `- Pass. Thru | Enable- ADAccount. The highlighted portion of this command takes the string “Pa$$w. New. Password parameter of the Set- ADAccount.
Password cmdlet. The - Force parameter is needed to suppress the confirmation prompt generated by use of the - As. Plain. Text parameter. Bulk creation of user accounts, computer accounts, groups, and other types of directory objects involves two steps: Creating the source file with the information for the accounts that need to be created. Creating the command or script that takes the source file and uses it to bulk- create the new accounts.
The CSV format used in the example in this section is a universal format supported by numerous applications, including Microsoft Excel, Microsoft Access, and even Microsoft SQL Server. By using a program like Excel to create the source information and save it in CSV format, you can quickly and easily bulk- create accounts in Active Directory. Example 5: Create new user accounts from a template account.
A template account is an account you use as a basis for creating other accounts. By configuring template account properties that are common to the other accounts you need to create, you can reduce the amount of information you need to provide for creating the additional accounts. For example, you could configure properties like the account expiration date and password options in a template account if these will be the same for the other user accounts you need to create. You may also configure properties like Company, Address, City, and Country in the template account. By doing this, you won’t need to specify these properties when you create the other user accounts.
TIP: One of the practice exercises at the end of Chapter 5 of my book Training Guide: Installing and Configuring Windows Server 2. R2 demonstrates how you can create a template account and then use it as a basis for creating additional user accounts. Some Additional Tips. Finally, here a couple additional tips I’ve gleaned from my colleagues in IT and from readers of our newsletter WServer. News. com. Copying user account properties.
You can copy the properties of one user account to create another. In effect you are using the first user account as a template for creating the second one. But you need to be careful when you try to do this.