Solved

Transfer tables from Access to Ms sql

Posted on 2011-09-09
14
283 Views
Last Modified: 2012-05-12
Hi Guys
I MOVE FREQUENTLY tables from access 2003 to a LOCAL ms sql 2005 server.
I use the 'TransferDatabase' Access macro command.
This macro will not overwrite an existing table, forcing me always to delete it first.
Is there any code or tips I can use to avoid this extra step of table deletions?
Your help is appreciated
Thanks
Dory
0
Comment
Question by:dory550
[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
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 3

Expert Comment

by:exceter
ID: 36509185
Why do not you use Import/Export of MS SQL Server ?
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509254
MS SQL Server provides a powerful data transfer tool that's able to import/export data from/to Access, Excel, text files, Oracle, other MS SQL Server databases/servers and almost any database system you have installed on the computer.

To use it you only need to right-click on the MS Sql Server target database name, select All Tasks -> Import on the context menu and follow the wizard.

This tool allows to create new tables, overwrite data to tables or append it. You can also programatically launch this process with an automated task.
0
 

Author Comment

by:dory550
ID: 36509314
OOOOOOOPS
Forgot to mention
I am using MS SQL EXPRESS 2005
Dory
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Expert Comment

by:exceter
ID: 36509389
In that case your solution is to write a script to copy from Access to SQL Server
0
 
LVL 10

Expert Comment

by:plummet
ID: 36509391
Hi Dory

I would write a VBA function to delete a SQL server table, using ADO. Are  you familiar with this technology? You could then call it with a tablename to delete whichever tables you want from the SQL server.

Something like this:

Function DeleteTable(sTablename As String) As Boolean

    Dim sSQL As String
    Dim conADO As New ADODB.Connection

    conADO.Open "your connection string in here"
        
    sSQL = "drop table " & sTablename
    
    conADO.Execute sSQL, dbSeeChanges
    
    Set conADO = Nothing
    DeleteTable = True    
End Function

Open in new window


Then call this function like this:

deletetable "mytablename"

Hope that's useful.

Regards
0
 
LVL 3

Expert Comment

by:exceter
ID: 36509409
Look here - http://bytes.com/topic/access/answers/507875-copying-data-access-sql-server

say a one field/one table thingie, create an SP

CREATE PROCEDURE dbo.uspCopyTable
AS

INSERT INTO
dbo.mytable (myfield)

SELECT
myfield
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\mypath\mydb.mdb';'admin';'mypwd', mytable)
GO

fire it off by something like this

dim cn as adodb.connection
set cn = new adodb.connection
cn.open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
cn.execute "dbo.uspCopyTable"
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 36509486
Import/export tools are included in SQL Server Management Studio, wich I think you can install into a MS SQL Server Express 2005 (I have a 2008 express installed at home with Management Studio).
0
 

Author Comment

by:dory550
ID: 36516243

I tried your solution (see below)  but I keep getting error 424 (Object required)
executing line 4
[ Set ObjConn = Server.CreateObject("ADODB.Connection")  ]
Attached is an image of my library references
Thanks
 Dory

Function deletetable()
Dim sSQL As String
Dim ObjConn As New ADODB.Connection
Set ObjConn = Server.CreateObject("ADODB.Connection")
 ObjConn.Open "DSN=pkpkc"
    sSQL = "drop table " & flagm
    ObjConn.Execute sSQL, dbSeeChanges
    Set Ogjconn = Nothing
    deletetable = True
End Function



refs.gif
0
 
LVL 10

Expert Comment

by:plummet
ID: 36516285
Hi Dory

You don''t need the line

Set ObjConn = Server.CreateObject("ADODB.Connection")

And also, if your table is called "Flagm" then the SQL should be "Drop table Flagm". Otherwise Flagm is a variable and should contain the name of the table to drop.

I think that should work.
0
 

Author Comment

by:dory550
ID: 36516316
plummet
It is working now thank you
Can you please modify the code below to check if table 'flagm' exists
before 'dropping'  it?
Thanks
Dory

Function deletetable()
Dim sSQL As String
Dim ObjConn As New ADODB.Connection
'Set ObjConn = Server.CreateObject("ADODB.Connection")
 ObjConn.Open "DSN=pkpkc"
    sSQL = "drop table flagm"
    ObjConn.Execute sSQL, dbSeeChanges
    Set Ogjconn = Nothing
 End Function
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36516457
Change (no points please):
sSQL = "drop table flagm"

To:
sSQL = "IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'flagm'); DROP TABLE flagm"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36516463
I seriously doubt this is going to be a problem, but if you did have two tables named flagm with different schemas, this would be more correct:
sSQL = "IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'flagm' AND SCHEMA_NAME(schema_id) = 'YourSchemaNameGoesHere'); DROP TABLE flagm"
0
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
ID: 36516470
Ok, here you go:
Function DeleteTable()
Dim sSQL As String
Dim ObjConn As New ADODB.Connection
 ObjConn.Open "DSN=pkpkc"
    sSQL = "if exists (select 1 from information_schema.tables where table_type='base table' "
    sSQL = sSQL & " and table_name='flagm') drop table flagm"
    ObjConn.Execute sSQL, dbSeeChanges
    Set ObjConn = Nothing
 End Function 

Open in new window


This checks before dropping the table. Also please notice the spelling of "Objconn" in the "Set Objconn=nothing" line as it was spelled incorrectly in your code.

Good luck with it - let me know how you get on.
0
 

Author Closing Comment

by:dory550
ID: 36516695
plummet
I have only one table called 'flagm'
'flagm1' was just a typo
Anyway
Everything is running fine now
Thank you for your help
Dory
ps
Also double thanks for catching the missspelling of Objconn=nothing
strange it did not produce an error on execution
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

739 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