Solved

Transfer tables from Access to Ms sql

Posted on 2011-09-09
14
282 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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