• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

MS Access Database Publishing to SQL Server using VB.

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
somesh_dutt
Asked:
somesh_dutt
1 Solution
 
mlmccCommented:
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
 
TheAnswerManCommented:
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
 
TheAnswerManCommented:
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
Technology Partners: 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!

 
TheAnswerManCommented:
0
 
gbarenCommented:
AnswerMan,

Please stop locking questions. You know better.

gbaren
0
 
gbarenCommented:
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
 
gbarenCommented:
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
 
gbarenCommented:
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
 
costelloCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now