• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Transfer tables from Access to Ms sql

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
dory550
Asked:
dory550
  • 4
  • 3
  • 3
  • +2
1 Solution
 
exceterCommented:
Why do not you use Import/Export of MS SQL Server ?
0
 
BardobraveCommented:
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
 
dory550Author Commented:
OOOOOOOPS
Forgot to mention
I am using MS SQL EXPRESS 2005
Dory
0
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.

 
exceterCommented:
In that case your solution is to write a script to copy from Access to SQL Server
0
 
plummetCommented:
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
 
exceterCommented:
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
 
BardobraveCommented:
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
 
dory550Author Commented:

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
 
plummetCommented:
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
 
dory550Author Commented:
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
 
Anthony PerkinsCommented:
Change (no points please):
sSQL = "drop table flagm"

To:
sSQL = "IF EXISTS(SELECT 1 FROM sys.tables WHERE name = 'flagm'); DROP TABLE flagm"
0
 
Anthony PerkinsCommented:
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
 
plummetCommented:
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
 
dory550Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now