dory550 lambert
asked on
Replace tables in MS SQL via MSaccess 2003
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
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
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?
ASKER
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
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
That is mean you need you import Table from SQL server Right ?
ASKER
Wrong!
I am exporting tables from Access to MS SQL NOT IMPORTING
Dory
I am exporting tables from Access to MS SQL NOT IMPORTING
Dory
What would you like to do Tables Structures or Tables data with same table structures (Sql server and MS Access)
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...
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...
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:
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.
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
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.
ASKER
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\SQLEX PRESS;Data base=tking ;Trusted_C onnection= 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
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\SQLEX
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
thanks
Dory
Dory
You are most welcome Dory! I wish you success with your project!