Access To SQL Conversion

rfurman1161 · March 28th, 2006, 07:48 PM

Hey Everyone,

What is the best way to move an Access databas over to SQL? I have one database in mind that is having allot of data in a couple of the tables. I have tried the Upsizing Wizard and that seems great for the intital structure and data, but I still see code that needs updated. Here is some of what I'm dealing with:

Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim strSQL As String
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    DoCmd.SetWarnings off

    If fExistTable("tbl_DSY") Then
        DoCmd.DeleteObject acTable, "tbl_DSY"
    End If

    'Contains Sales data for the year
    DoCmd.TransferText acImportDelim, "DSY Import Specification", "tbl_DSY", "P:\TXT\DSY.TXT", False, ""

    'Delete old table
    If fExistTable("tbl_PABP") Then
        DoCmd.DeleteObject acTable, "tbl_PABP"

This is getting new tables created in Access, but not in SQL. What exactly am I still needing to do for this all to go to SQL?

Robert

Bob Bedell · March 28th, 2006, 11:50 PM

Kind of a thorny problem. DoComd.TransferText can only be used with SQL Server from an ADP (not an MDB). So when you upsized, you'd need to choose the option to create an ADP (not linked tables). However, import specifications aren't supported by ADPs, they are stored in Jet tables and ADPs don't use Jet.

To import a text file into SQL Server with VBA, consider using VBA to execute a DTS Package on SQL Server. You can reference the Microsoft DTSPackage Object Library from VBA code, and here is a link that explains how to execute a package:

http://www.sqldts.com/default.aspx?208

You could also execute a stored procedure to do the import from either an ADP or an MDB (using pass-through queries).

The Upsizing Wizard is what it is, good at transferring Access tables to SQl Server, and little else. You can also just import the Access DB into SQL Server using DTS.

HTH,

Bob

rfurman1161 · April 2nd, 2006, 10:16 PM

Bob,

Thanks for all the help.

On another subject, I'm trying to put most of my efforts on learning VB Web Devloper 2005. Although the many tutorials are great, I seem to be struggling with allot of the syntax with VB, object refrences, etc. Am I missing something before I dive into all of this? I did buy one of the VB books that you recommended so that will help over the lon run, Is there a good book you could recommend for the beginner that would hlp fill in the gaps that I seem to be missing?

Thanks,

Robert

Quote:

quote:Originally posted by Bob Bedell
Kind of a thorny problem. DoComd.TransferText can only be used with SQL Server from an ADP (not an MDB). So when you upsized, you'd need to choose the option to create an ADP (not linked tables). However, import specifications aren't supported by ADPs, they are stored in Jet tables and ADPs don't use Jet.

To import a text file into SQL Server with VBA, consider using VBA to execute a DTS Package on SQL Server. You can reference the Microsoft DTSPackage Object Library from VBA code, and here is a link that explains how to execute a package:

http://www.sqldts.com/default.aspx?208

You could also execute a stored procedure to do the import from either an ADP or an MDB (using pass-through queries).

The Upsizing Wizard is what it is, good at transferring Access tables to SQl Server, and little else. You can also just import the Access DB into SQL Server using DTS.

HTH,

Bob

Bob Bedell · April 3rd, 2006, 06:17 AM

Hi Robert,

First off, it appears that Microsoft marketing magic has managed to confuse the heck out of me again there for a minute. I guess they've decided to drop the ".NET" tag with the release of Visual Studio 2005. So when you say "VB", I was originally thinking you meant "classic VB". But I guess "Visual Basic 2005" is now VB, which used to be VB.NET. Are you with me? Good. I'm not.

Anyway, VB Web Developer I quess is the new name for what was "Web Matrix" with .NET 1.0 -1.1.

That being said, I'm assuming you're looking for books on Visual Basic 2005 and ASP.NET 2.0, which are the languages you program VB Web Developer with.

I tend to try and find "the smartest" authors on a particular language, and then stick with them. And the two I learned the most from when learning VB.NET and ASP.NET we're Francesco Balena (VB.NET) and Dino Esposito (ASP.NET). They are both thorough, in-depth, comprehensive, brilliant.

Their new books for the .NET Framework 2.0 are:

Balena - Programming Microsoft Visual Basic 2005: The Language (Paperback)

Esposito - Programming Microsoft ASP.NET 2.0 Core Reference (Paperback)

Balena's .NET 1.0 - 1.1 book covered EVERYTHING - from variable declaration, to control flow, to class fundamentals, error handling, inheritance, all of it. He's my recommendation for VB.NET/Visual Basic 2005.

I've just begun to look at C# 2005, but when I get up to speed on VB 2005, I'll be reading Balena's new book.

HTH,

Bob

Bob Bedell · April 3rd, 2006, 10:49 PM

I put together an ASPX page that pretty much mirrors:

Code:

If fExistTable("tbl_DSY") Then
        DoCmd.DeleteObject acTable, "tbl_DSY"
    End If

    'Contains Sales data for the year
    DoCmd.TransferText acImportDelim, "DSY Import Specification", "tbl_DSY", "P:\TXT\DSY.TXT", False, ""

It enables you to import the CSV file into SQL Server from a Web app by calling a stored procedure. The sproc is executed using ADO.NET and VB.NET in-line script. Here is the sproc to create on SQL Server:

Code:

CREATE PROCEDURE procImportCSVFIle
AS

IF OBJECT_ID('ImportTable') IS NOT NULL
DROP TABLE ImportTable 
 
SELECT *
INTO ImportTable
FROM
    OPENROWSET('MSDASQL',
        'Driver={Microsoft Text Driver (*.txt; *.csv)};
        DEFAULTDIR=C:\Test\;Extensions=CSV;',
        'SELECT * FROM TestCSVFile.csv')

The first column of the CSV file will be used as column headers when the table is created.

And here is the ASPX page (I'm using ASP.NET 1.1):

Code:

<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.SQLClient" %>
<%@ Page Language="VB" AutoEventWireup="True" %>

<html>
<head>
   <script language="VB" runat=server>
    Sub ImportBtn_Click(sender As Object, e As EventArgs)
        Dim strConnectionString As String  = "server=(local);database=NorthwindTables;integrated security=true;"
        Dim cnn As New SQLConnection(strConnectionString)

        Try
            cnn.Open()

            Dim cmd As SQLCommand = cnn.CreateCommand()

            With cmd
                .CommandText = "procImportCSVFIle"
                .CommandType = CommandType.StoredProcedure
                .ExecuteNonQuery()
            End With

        Catch ex As Exception
            Response.Write(ex.message)
            Response.End
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Response.Write("The file was imported successfully.")
        Response.End
    End Sub 
  </script>
</head>
<body>
   <form runat="server" ID="Form1">

      <h3>Import CSV File into SQL Server</h3>

      <asp:Button id="Button1"
           Text="Import CSV File"
           OnClick="ImportBtn_Click" 
           runat="server"/> 
   </form>
</body>
</html>

Assign the ASPNET user on SQL Server to the db_ddladmin role so it can execute data definition language statments (CREATE TABLE in this case).

HTH,

Bob

Bob Bedell · April 3rd, 2006, 11:05 PM

Wow...I guess HTML gets pretty mangled when P2P ships it out as e-mail. Anyway, its formatted correctly in the post.

Bob

rfurman1161 · April 7th, 2006, 10:53 AM

Thanks again Bob. I haven't had the chance to test this yet on my end, but I did get the proc created. Seems like that will take care of the main problem of getting the data into SQL. Two questions though. #1, I have one area that imports 12 different text files into one table into Access. Can that same proc be amended so that all 12 text files can be imported by one proc? #2, I'm using .NET 2.0, is much of that caaode will be in the web.config correct?

Thanks again

Robert

Bob Bedell · April 8th, 2006, 04:17 PM

Hi Robert,

The stored procedure was for importing a single text file into SQL Server. Are you saying you also want to import 12 text files into an Access database?

As for the .NET code compatibility issue, I would be astonished if any of the code in the .aspx page I posted doesn't run on version 2.0 of the Framework. I've downloaded 2.0, but haven't compiled any .aspx pages on it yet (something to play with tomorrow if I get a chance). The page is really just a vanilla ADO.NET script with a single ASP.NET server control (a Button). Shouldn't present any conversion problems.

Bob

rfurman1161 · April 10th, 2006, 11:48 AM

Bob,

Unfortuntely yes. The origial application that i'm dealing with is written in RPG. It basically stores it's information in flat files. Threse files are exported through a utility that is run in a script nightly so that we caan have access to the data in Access. The application we use is called Databed. We will eventually be going to an application called Infinite36 which is made by Californiasoftware.com. Unfortunately they have limited support of Databed. So seeing this what would be the bet way to implement these procs?

Thanks

Robert

Quote:

quote:Originally posted by Bob Bedell
Hi Robert,

The stored procedure was for importing a single text file into SQL Server. Are you saying you also want to import 12 text files into an Access database?

As for the .NET code compatibility issue, I would be astonished if any of the code in the .aspx page I posted doesn't run on version 2.0 of the Framework. I've downloaded 2.0, but haven't compiled any .aspx pages on it yet (something to play with tomorrow if I get a chance). The page is really just a vanilla ADO.NET script with a single ASP.NET server control (a Button). Shouldn't present any conversion problems.

Bob

Bob Bedell · April 11th, 2006, 06:35 AM

Unfortunately you can't implement the stored procedure at all in Access. It doesn't support stored procedure functionality (beyond the most rudimentary DML statments), just some ANSI-92 sored procedure syntax.

So you're back to DoCmd.TransferText, BASIC I/O, or the FileSystem object to automate the file import into Access using VBA, or using VB.NET, the System.IO namespace and either the StreamReader or BinaryReader class.

Bob