Convert to SQL from Access-SCRIPT?

Results 1 to 2 of 2

Thread: Convert to SQL from Access-SCRIPT?

  1. #1
    Join Date
    Dec 1969

    Default Convert to SQL from Access-SCRIPT?

    I know in enterprise manager there is a import tables from access option but I was wondering if any one knew of a free script that would generate a SQL Script to create the SQL database, tables, and fields that I could run on a SQL server.<BR>Did I confuse you?<BR>Try 2.<BR>I have been working on a large project that initially started being small, but it grew. I started with an Access DB and that was fine, but now I want to convert it to a SQL Server. I may not us MS SQL so I want to create the actual SQL Script that creates the SQL db not just import the design from Access. I want to run a Script on the access DB to create my Script for my SQL server. I know this it possible to do with an ASP Page, but I figured someone has already done this, and has it on some site somewhere. Anyone have any ideas and sorry if I totally confused you.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Basics easy, devil in the details

    The real problem is that various DBs have various requirements for some field types. <BR><BR>For example, where I can use INTEGER as a field type with Access, SQL Server, and MySQL, I have to use a different field type with Oracle! (I did it a while ago, but I think I used NUMBER(12,0) or DECIMAL(12,0) ... something along those lines.)<BR><BR>Ummm...or maybe it was DOUBLE that worked with the first three but that I had to change to NUMBER(12,2) with Oracle.<BR><BR>Well, whatever. The point is that the ideal field type for one DB is not necessarily the right type for another DB.<BR><BR>Other than that...<BR><BR>I simply wrote a quicky little hack program. I gave it a DSN (or .mdb file name) and a table name, and it read the schema for that table followed by reading all the contents and spewed out a text file that looked something like:<BR><BR>CREATE TABLE employees (empID INTEGER, empName VARCHAR(40), ...)<BR>INSERT INTO employees (1, &#039;Adams&#039;, ...)<BR>INSERT INTO employees (2, &#039;Bones&#039;, ...)<BR><BR>Then I wrote a simple little ASP page that could connect to a different DSN and would read the lines in the text file one at at time, passing them to "otherConnection.Execute( textFileLine )".<BR><BR>*BUT* <BR><BR>But as I noted, I had to adapt the little program that produced the text file to produce SQL that was compatible with the destination DB.<BR><BR>If I&#039;d been just a little smarter, my text file would have used "tokens" (e.g., $D for DOUBLE, $T for TEXT, $I for INTEGER, etc.) and then the program that did the EXECUTE calls would have substituted (using REPLACE) the right datatypes for the DB being created. <BR><BR>Anyway, I&#039;d suggest the you *do* code this up yourself! It&#039;s a fine learning experience, and you can probabably write it in a way that will make more sense to you later, when it&#039;s time to adapt it to another DB.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts