Solved

MS Access Database Publishing to SQL Server using VB.

Posted on 2001-06-22
9
192 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
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

740 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