?
Solved

Replace tables in MS SQL via MSaccess 2003

Posted on 2011-09-28
12
Medium Priority
?
258 Views
Last Modified: 2012-05-12
Hi guys
I am using VBA to replace tables in sql 2005 and having a problem.
This action consists of 3 steps:
1 Delete the tables to be replaced.
2. Refresh the Database.
3. Transfer replacement tables.
I using VBA code for steps 1 & 3  which works fine.
Is there any  code I can use for step 2?
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
12 Comments
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36812995
Which database are you refreshing?  The Access file, or the SQL Server database?  Also, what exactly are you trying to refresh? --- a display of information?
0
 

Author Comment

by:dory550
ID: 36813109
datAdrenaline
I am trying to replace SQL tables with Access tables
I delete the SQL table with vba code but  keep getting "Over Write" error
Even though  tables were deleted,
No error occurs if I physically refresh database after table deletions.
I need to clear display of information
Dory



0
 

Expert Comment

by:Ashrafedes
ID: 36813158
That is mean you need you import Table from SQL server Right ?
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:dory550
ID: 36813219
Wrong!
I am exporting tables from Access to MS SQL NOT IMPORTING
Dory
0
 
LVL 5

Expert Comment

by:AlokJain0412
ID: 36813672
What would you like to do Tables Structures or Tables data with same table structures (Sql server and MS Access)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36815239
please be more specific in your requirements...

delete is not the same as drop when talking about databases and tables...

what are you doing ?

    deleting all the data on a table
              - the table structure remains (note for sql server you may wish to use TRUNCATE instead of DELETE)
   or dropping the table
              - the physical table (including its data) is removed from the database.

why would you attempt this via VBA the standard approach would be to generate script files for the DBA to apply...
alternatively a SSIS package would be developed...
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36817106
Since you are getting the Overwrite error and you indicate you are EXPORTING your Access tables to SQL Server, it is my guess that you are not DROPing your SQL Server tables and you are simply DELETEing the records within them.  So with that being the case you can move your Acces (Jet/ACE) data by issuing a SQL statement that INSERTs your Access data IN an existing SQL Server table with a single SQL statement.  The VBA to do such a task looks like this:
Public Sub AccessToSQLServer(strAccessTableName As String, strSQLServerTableName As String)
    
    Dim strSQL As String
    Dim strODBCConnect As String
    
    'Define the connect string (same as what a linked table connect property would be)
    strODBCConnect = "[ODBC" & _
                     ";DRIVER=SQL Server;SERVER=someServernameAndInstance" & _
                     ";DATABASE=someSQLDatabase;Network=DBMSSOCN" & _
                     ";Trusted_Connection=Yes]"
    
    
    'Build the INSERT INTO SQL Statement that will export the data to SQL Server
    'through the "portal" query we just modified
    strSQL = "INSERT INTO " & strODBCConnect & ".[" & strSQLServerTableName & "]" & _
             " SELECT * FROM [" & strAccessTableName & "]"
             
    'Execute the statment, which will dump the Access data to SQL Server
    CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
    
End Sub

Open in new window


The assumption is that the source and destination tables have the same field names and the field names are defined in the same order.  If that is not the case, then you will have to add the fields to the IINSERT INTO clause and SELECT clauses respectively.  Alos, you will have to substitute the "some*" references in the strODBCConnect variable to reflect your configuration.

0
 

Author Comment

by:dory550
ID: 36897810
datAdrenaline
Thanks for your input
Sorry I took so long to response
Anyway
I AM DROPPING THE TABLES!
Here is a sample of the code I am using to remove and replace a sql table:


Function Movetable2Sql()
deletetSQLables 'see function below
'I need stop after I drop table, refresh the database and resume
'If not I get an error
stop
    DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DRIVER={SQL Server};SERVER=TECRA\SQLEXPRESS;Database=tking;Trusted_Connection=Yes", acTable, "picture", "picture", False
End Function

Function deletetSqlTables()
On Error Resume Next
Dim sSQL As String
Dim ObjConn As New ADODB.Connection
ObjConn.Open "DSN=pkpkc"
    sSQL = "drop table picture"
    ObjConn.Execute sSQL, dbSeeChanges
        Set Ogjconn = Nothing
    deleteSqlTables = True
    On Error GoTo 0
End Function


0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 2000 total points
ID: 36900645
I personally don't like to DROP things I am going to just recreate.  I consider it a greater risk to mess with the schema than to work with existing schema and just delete / re-insert data.  With that in mind, maybe the following code would be an option for you:
Public Sub AccessToSQLServer(strAccessTableName As String, strSQLServerTableName As String)
    
    Dim strSQL As String
    Dim strODBCConnect As String
    Dim db As DAO.Database
    
    Set db = CurrentDb
    
    'Define the connect string (same as what a linked table connect property would be)
    strODBCConnect = "[ODBC" & _
                     ";DRIVER=SQL Server;SERVER=TECRA\SQLEXPRESS" & _
                     ";DATABASE=tking;Network=DBMSSOCN" & _
                     ";Trusted_Connection=Yes]"
    
    'Build the SQL to delete all records in the destination table and execute it.
    strSQL = "DELETE FROM " & strODBCConnect & ".[" & strSQLServerTableName & "]"
    db.Execute strSQL, dbFailOnError + dbSeeChanges
    
    'Build the INSERT INTO SQL Statement that will export the data to SQL Server
    strSQL = "INSERT INTO " & strODBCConnect & ".[" & strSQLServerTableName & "]" & _
             " SELECT * FROM [" & strAccessTableName & "]"
             
    'Execute the statment, which will dump the Access data to SQL Server
    db.Execute strSQL, dbFailOnError + dbSeeChanges
    
End Sub

Open in new window


Back to your original question of "Refresh the database" ... we have expressed that we really don't know what you are meaning here --- do you want to refresh the list of tables from somewhere or ??? What needs to be refreshed after your code runs -- or rather what does not reflect what you expect see, and when do you expect to see it?
0
 

Author Comment

by:dory550
ID: 36900788

datAdrenaline
After checking again I discovered my error
my code works ok now
no need to refresh database,
However
I read your comment and agree with it
Sooo
I am replacing my code to match your reccomendations.
Thank you for your help
Dory

0
 

Author Closing Comment

by:dory550
ID: 36900791
thanks
Dory
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36900797
You are most welcome Dory!  I wish you success with your project!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 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