Solved

Transfer tables from Access to Ms sql

Posted on 2011-09-09
14
277 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now