|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
March 28th, 2006, 07:48 PM
|
Authorized User
|
|
Join Date: Mar 2006
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Access To SQL Conversion
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
|
March 28th, 2006, 11:50 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|
April 2nd, 2006, 10:16 PM
|
Authorized User
|
|
Join Date: Mar 2006
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
April 3rd, 2006, 06:17 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|
April 3rd, 2006, 10:49 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|
April 3rd, 2006, 11:05 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Wow...I guess HTML gets pretty mangled when P2P ships it out as e-mail. Anyway, its formatted correctly in the post.
Bob
|
April 7th, 2006, 10:53 AM
|
Authorized User
|
|
Join Date: Mar 2006
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
April 8th, 2006, 04:17 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|
April 10th, 2006, 11:48 AM
|
Authorized User
|
|
Join Date: Mar 2006
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
April 11th, 2006, 06:35 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
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
|
|
|