Solved

MS Access Database Publishing to SQL Server using VB.

Posted on 2001-06-22
9
200 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 101

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

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!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

691 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