Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Transfer tables from Access to Ms sql

Posted on 2011-09-09
14
Medium Priority
?
289 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

972 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