?
Solved

MS Access Database Publishing to SQL Server using VB.

Posted on 2001-06-22
9
Medium Priority
?
207 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 900 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

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!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

765 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