Solved

MS Access Database Publishing to SQL Server using VB.

Posted on 2001-06-22
9
195 Views
Last Modified: 2008-02-07
I've made a program to Publish my MS-Access database to the SQL-Server. It converts all the properties of MS-Access database to SQL-Server database. But it is very slow due to two dimensional looping (Table-Records and Table-Fields ) and running queries to insert each record.

Is there any method to insert all the records of MS-Access database (on Lan) to the SQL Server database (on Web).

I've tried "Select.. INTO.." SQL statement (It makes the table and inserts records from one databse to another) but I don't know how to pass my SQL-Server database reference to it.

1) Please tell me the complete command based on above statement, by taking the following details -
Table-Name = Customers (All Records)
SQL-Server address = 111.222.33.444
SQL-Server Database   = MySQLDB
SQL-Server User ID    = sqluser
SQL-Server Password   = sqlpassword

2) Or tell me another way of pulishing (with upsizing) of MS-Access database to SQL-Server database, using Visual Basic6 and SQL queries.

0
Comment
Question by:somesh_dutt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 6219059
MS SQL has a Data Transmission Service which will allow you to create tables in MS SQL and then copy the data directly from your Access database.  You have to provide the SQL statements to create the tables and then the select statement to copy the code.  

If the SQL tables already exist then you only need to copy the data.

good luck
mlmcc
0
 
LVL 3

Expert Comment

by:TheAnswerMan
ID: 6219134
You can use DTS from SQL Server..  and select your Access database as the source..

If you can just use DTS to create the table.. then
you could just add linked tables to the SQL server from access.. and copy the data that way.. faster..


as a third way.. you can use your loop..
and use SQL-DMO library.. to create the tables..etc..

here is a sample..


On Error GoTo ErrorHandler
Dim oDatabase As SQLDMO.Database
'New table
Dim oMyTable As New SQLDMO.Table
'New table columns
Dim colMyTableID As New SQLDMO.Column
Dim colMyTableName As New SQLDMO.Column
Dim colMyTableDescription As New SQLDMO.Column

Dim sDatabase As String
sDatabase = sYourDatabaseName
' Get the database.
Set oDatabase = goSQLServer.Databases(sDatabase)

' Populate the Column objects to define the table columns.
colMyTableID.Name = "MyTableID"
colMyTableID.Datatype = "int"
colMyTableID.Identity = True
colMyTableID.IdentityIncrement = 1
colMyTableID.IdentitySeed = 1
colMyTableID.AllowNulls = False

colMyTableName.Name = "MyTableName"
colMyTableName.Datatype = "varchar"
colMyTableName.Length = 15
colMyTableName.AllowNulls = False

colMyTableDescription.Name = "MyTableDescription"
colMyTableDescription.Datatype = "text"
colMyTableDescription.AllowNulls = True


' Name the table, then set desired properties to control eventual table
' construction.
oMyTable.Name = "MyTable"
oMyTable.FileGroup = "PRIMARY"

' Add populated Column objects to the Columns collection of the
' Table object.
oMyTable.Columns.Add colMyTableID
oMyTable.Columns.Add colMyTableName
oMyTable.Columns.Add colMyTableDescription
oMyTable.Columns.Add colMyTablePicture

' Create the table by adding the Table object to its containing
' collection.
oDatabase.Tables.Add oMyTable
'
'
'In a second step (this can be done during table creation)
'Now create a PRIMARY key
Dim keyPKMyTable As New SQLDMO.Key
Dim namesPKMyTable As SQLDMO.Names

' Create the primary, clustered key on CategoryID.
keyPKMyTable.Clustered = True
keyPKMyTable.Type = SQLDMOKey_Primary

' Use the Names collection to define the constraint on the
' CategoryID column.
Set namesPKMyTable = keyPKMyTable.KeyColumns
namesPKMyTable.Add "MyTableID"

' Mark start of change unit.
oMyTable.BeginAlter

' Add the populated Key object to the Keys collection of the
' Table object.
oMyTable.Keys.Add keyPKMyTable

' Create the PRIMARY KEY constraint by committing the unit of change.
oMyTable.DoAlter

Exit Sub

ErrorHandler:
MsgBox Err.Description

End Sub
0
 
LVL 3

Accepted Solution

by:
TheAnswerMan earned 300 total points
ID: 6219170
You can use DTS from SQL Server..  and select your Access database as the source..

If you can just use DTS to create the table.. then
you could just add linked tables to the SQL server from access.. and copy the data that way.. faster..


as a third way.. you can use your loop..
and use SQL-DMO library.. to create the tables..etc..

here is a sample..


On Error GoTo ErrorHandler
Dim oDatabase As SQLDMO.Database
'New table
Dim oMyTable As New SQLDMO.Table
'New table columns
Dim colMyTableID As New SQLDMO.Column
Dim colMyTableName As New SQLDMO.Column
Dim colMyTableDescription As New SQLDMO.Column

Dim sDatabase As String
sDatabase = sYourDatabaseName
' Get the database.
Set oDatabase = goSQLServer.Databases(sDatabase)

' Populate the Column objects to define the table columns.
colMyTableID.Name = "MyTableID"
colMyTableID.Datatype = "int"
colMyTableID.Identity = True
colMyTableID.IdentityIncrement = 1
colMyTableID.IdentitySeed = 1
colMyTableID.AllowNulls = False

colMyTableName.Name = "MyTableName"
colMyTableName.Datatype = "varchar"
colMyTableName.Length = 15
colMyTableName.AllowNulls = False

colMyTableDescription.Name = "MyTableDescription"
colMyTableDescription.Datatype = "text"
colMyTableDescription.AllowNulls = True


' Name the table, then set desired properties to control eventual table
' construction.
oMyTable.Name = "MyTable"
oMyTable.FileGroup = "PRIMARY"

' Add populated Column objects to the Columns collection of the
' Table object.
oMyTable.Columns.Add colMyTableID
oMyTable.Columns.Add colMyTableName
oMyTable.Columns.Add colMyTableDescription
oMyTable.Columns.Add colMyTablePicture

' Create the table by adding the Table object to its containing
' collection.
oDatabase.Tables.Add oMyTable
'
'
'In a second step (this can be done during table creation)
'Now create a PRIMARY key
Dim keyPKMyTable As New SQLDMO.Key
Dim namesPKMyTable As SQLDMO.Names

' Create the primary, clustered key on CategoryID.
keyPKMyTable.Clustered = True
keyPKMyTable.Type = SQLDMOKey_Primary

' Use the Names collection to define the constraint on the
' CategoryID column.
Set namesPKMyTable = keyPKMyTable.KeyColumns
namesPKMyTable.Add "MyTableID"

' Mark start of change unit.
oMyTable.BeginAlter

' Add the populated Key object to the Keys collection of the
' Table object.
oMyTable.Keys.Add keyPKMyTable

' Create the PRIMARY KEY constraint by committing the unit of change.
oMyTable.DoAlter

Exit Sub

ErrorHandler:
MsgBox Err.Description

End Sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:TheAnswerMan
ID: 6219250
0
 
LVL 5

Expert Comment

by:gbaren
ID: 6220797
AnswerMan,

Please stop locking questions. You know better.

gbaren
0
 
LVL 5

Expert Comment

by:gbaren
ID: 6220799
You can attach an SQL Server table to your Access database and use an Access query to publish the data.

This should be the simplest and most straightforward method.

gbaren
0
 
LVL 5

Expert Comment

by:gbaren
ID: 6220807
The SQL Statement, once the table is attached would be:

insert into sql_table(c1,c2,c3,c4)
select c1,c2,c3,c4 from access_table where update_date > last_update_date

Not knowing your table structure, I'm using c1-c4 instead of column names you want to transfer. You'll need to supply your own WHERE also.

gbaren
0
 
LVL 5

Expert Comment

by:gbaren
ID: 6220810
I see you are brand new to this forum.

Please click the REJECT option for TheAnswerMan's proposed answer. He isn't playing nice. When I ask a question, I always make it a point to reject ANY proposed answer, even if it solves my problem. You decide.

By the way, if you found TheAnswerMan's proposed answer helpful and would still like to reject it as I suggested, please award the answer to mlmcc, the other participant on this thread.

gbaren
0
 

Expert Comment

by:costello
ID: 6400364
Rejecting TheAnswerMan's answer.

If no objections are made in the next 4 days, I will force accept mlmcc's comment.

costello
Community Support Moderator @ Experts-Exchange
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question