Posts Tagged ‘Access’

Let me start by stating :

It Works!

I choose, at times to set before myself some interesting goals. In this case it is the task of migrating a Microsoft Web Access Application to something with a bit more longevity. With Microsoft deciding that the Web Access platform is no longer useful to them they dropped it like a hot potato and ran, leaving those of us who decided to use the platform with very few options.

The way I learn stuff is by doing, I have little patience for tutorials, so I usually wind up learning new stuff by asking “How do I do that?” and then spending a few moments (or hours…) on the web figuring it out. That being said, this blog post is not meant to be a “How do I do this – Please hold my hand.” kind of thing. Rather it is more of a framework for future projects I may have with this platform. If however, you do need a little hand holding, drop a comment, and I’ll see what I can do for you. Better yet, check out this online book: it is a must if you know nothing about ROR…

What I used:

Ubuntu Server 16.04.3 (Installed on dedicated hardware):

SQL Server 2017 installed on Ubuntu Server:

Directions for SQL Server Ubuntu install:

Ruby and Rails:

my versions:

Ruby 2.4.1p111 (2017-03-22 revision 58053)
Rails 5.1.4

Database Connectivity for SQL Server:



Additional tools:



Server Connectivity and Development Tools

I am not a huge command line fan, but it is necessary to get all this working, here are the tools I use:





Some Initial Gotchas:

  • You may be tempted to build a RoR environment in windows, don’t. Ruby and thus Rails were developed in a Linux world. If you don’t want to do this in Linux, look elsewhere for your development environment.
  • ROR is very much a “The sum is greater then it’s parts.” kind of thing. All those “parts” are dependent on each other. Rails has many automated processes and can be a joy when building stuff, including the DB from scratch. It can be a bear, and loaded with gotcha’s if you try to do too many things “off the Rails” Review this example for what I am trying to say.
  • It is probably best with any NEW application, to build your DB in one of the three default DB engines. (SQLite, MySQL, or PostgreSQL) That being said, I had an existing DB and back office applications that were sitting on MS Access and needed to find a solution that easily meshed with that. The compromise was Microsoft’s SQL Server 2017 (installed on Linux)
  • If you are working with an existing DB, there are a few things you will need to be aware of:
    • RoR reserves the field name “type“. So, if you have a field in you DB named “type” you will need to address that before you do any importing or conversions. Alternately, if you cannot change the field name, you will need to add this line to “model” for any tables that include a field “type.”
      self.inheritance_column = "inheritance_type"

      as mentioned here:

    • If you, as I do, use CamelCase (ex: tMyTable or fFirstName) in your DB entity naming, you will not be able to use the automated process for scaffold building described in this article. The automated process will actually change CamelCase to snake_case “fFirstName” to “f_first_name” when creating the scaffold structure. I was able to go back and change these to all lowercase (ex: fFirstName to ffirstname) not as readable, but it works. The alternative is of course to build the scaffold by hand, but that will make the conversion much more difficult. Note: this applies to all naming in your DB including Index’s, and foreign keys.
    • Index names are automatically created in Access. You will probably want to go back and modify/simplify them for better automation in the connection and scaffold building process.
    • Rails is designed to build a DB from the ground-up, it has it’s own set of default functionality that will probably not mesh well with a pre built DB.
      • Rails likes to do all the heavy DB lifting, it wants to keep track or even manage all of your indexes, associations, primary keys, foreign keys, etc. It sees a DB as a dumb data store, with all the logic being cared for in you Rails models.
      • Rails assumes your Primary Key is a field labeled “id”
      • Rails assumes your Foreign Keys look like “mytable_id”, where “mytable” is the name of the table which defines the foreign key field(s), however this can be overridden.

Now the Fun!

After you have installed everything and created your new Rails application, you will need to be in your application’s root directory for Rails Commands to work.

railsuser@myserver:~$ cd railsapp

You will need to edit a couple of files to tell the application to use your SQL Server DB.

Rails DB Settings

Edit the Gemfile to include these two lines:

gem 'tiny_tds'
gem 'activerecord-sqlserver-adapter'

and tell your application where to look for the DB by placing this code in your /config/database.yml file:

   adapter: sqlserver
   host: # IP address pointing to the hardware where your SQL Server is located
   port: 1433 
   database: myDatabaseName  # Name of the DB you are using for the Application
   username: DBAdmin
   password: secret

Note: This is specifically marked as the “development:” environment, changes will need to made to the “production:” settings in this file as well when you are ready to migrate to your production environment.

Alternately, you can make these changes to the “default:” settings, this will cause all the associated “*default” environments to use that.

Rails DB Schema Import and Setup


Now you need to get the schema by running this command:

rails db:schema:dump

This will build the /db/schema.rb file. A surefire way of knowing that you installed everything correctly…

Next, to use the schema_to_scaffold gem type the following:


You will see something like this:

Looking for schema.rb in /home/railsuser/railsapp
0. /home/deploy/railsapp/db/schema.rb

Select a path to the target schema:



You will get something like this:

Loaded tables:
0. table01
1. table02

Options are:
4 for table 4; (4..6) for table 4 to 6; [4,6] for tables 4 and 6; * for all Tables

Select a table:



This will generate scripts for all the table scaffolds. (I found I was unable to use the range options – but that was probably me not typing what it was asking me to?)

Scaffold returns a list of scaffold creation scripts something like this:

rails generate scaffold table01 table02id:integer fieldxx:string --no-migration

rails generate scaffold table02 field01:integer field02:string --no-migration

The “–no-migration” part is important for us. If you create migration files, you are telling rails that you need these DB tables to be created or changed in some way. But we don’t need tables created or modified.

Next we need to run those scripts. If you are using Putty, simply highlight the script for a given table and right click on your mouse. This performs a cut/paste operation. Pressing enter will then run the script and create the scaffold for that table. It’s magic! Running these scaffold generator scripts will create things like “views” (web pages) and their controllers. It will also modify other files, such as /config/routes.rb

Pointing to the Default Web Page

Our next step is to change the default route (Home Page)

place a “root” definition in your /config/routes.rb file just below the “Rails.application.routes.draw do” line similar to this, but use the name of the controller and view you want to use as the root page.

root 'myviews#index'

We are pointing at the controller for the myview views. So the /config/routes.rb file should now start like this:

Rails.application.routes.draw do
  root 'myviews#index'

Now when you enter the url to your page in your browser – it will show the page you stipulated as your root page.

There is much more to do, but this will get you started.