September 20, 2005 at 3:23 am
I've pasted some code that a friend gave me for copying a table from an SQL server to a database held on a local pc, my question is, is there a way of doing this in reverse, i.e from a locally held database to the sql server?
Function Copy_Table_From_Sql_Server(ByVal NewTable As String, ByVal ExistingTable As String, ByVal LocalDb As Database) As Boolean
On Error GoTo ErrHandler
CopyTable: 'try and copy before deleting the existing table
'just incase it doesn't exist in the external db
Sql = "SELECT * INTO [" & NewTable & "] FROM " & _
"[ODBC;Driver=SQL Server; SERVER=10.234.95.121;DATABASE=Titan;UID=UserName;PWD=Password;].[" & ExistingTable & "];"
LocalDb.Execute Sql
Copy_Table_From_Sql_Server = True
Exit Function
DeleteTable:
LocalDb.Execute "drop table [" & NewTable & "] ;" ' drop the old table
GoTo CopyTable
ErrHandler:
Select Case Err
Case 3376
Resume Next ' table didn't exist to begin with
Case 3010
Resume DeleteTable ' table allready existed
Case 3078
Exit Function ' table doesn't exist in external db
Case Else
' MsgBox Err.Number & " " & Err.Description
End Select
End Function
September 20, 2005 at 6:57 am
Can't you just link the tables?
Can't you also just copy that code on that db on do the reverse process... or code and insert into select...
September 20, 2005 at 7:10 am
as far as I know it doesn't work in reverse, I have tried it without any success
September 20, 2005 at 7:17 am
You can't sue select into to get the data back, but you can do insert into select
September 20, 2005 at 7:21 am
can you explain that to me? sorry but I'm very new to this
September 20, 2005 at 7:26 am
Insert into tableName (col1, col2, col3) Select col1, col2, col3 from OtherTable. Looks like you can do something like that using the code you just presented.
September 20, 2005 at 8:00 am
ok thanks, I'll give that a try
September 21, 2005 at 4:02 am
no good, I've tried, is there anyone who can help me out with some code?
September 21, 2005 at 6:46 am
What did you try?
September 21, 2005 at 6:48 am
the code I pasted above but in reverse
September 21, 2005 at 6:55 am
Please POST IT.
September 21, 2005 at 6:59 am
Here's an alternate suggestion - why don't you script the "create table"...export the data to a file (word, excel..etc.) - run the script on your target database and import the data from the file?!?!
**ASCII stupid question, get a stupid ANSI !!!**
September 21, 2005 at 7:01 am
Function Copy_Table_From_Sql_Server(ByVal NewTable As String, ByVal ExistingTable As String, ByVal LocalDb As Database) As Boolean
On Error GoTo ErrHandler
CopyTable: 'try and copy before deleting the existing table
'just incase it doesn't exist in the external db
Sql = "SELECT * INTO "[ODBC;Driver=SQL Server; SERVER=10.234.95.121;DATABASE=Titan;UID=UserName;PWD=Password;].[" & ExistingTable & "];"
from Newtable
LocalDb.Execute Sql
Copy_Table_From_Sql_Server = True
Exit Function
DeleteTable:
LocalDb.Execute "drop table [" & NewTable & "] ;" ' drop the old table
GoTo CopyTable
ErrHandler:
Select Case Err
Case 3376
Resume Next ' table didn't exist to begin with
Case 3010
Resume DeleteTable ' table allready existed
Case 3078
Exit Function ' table doesn't exist in external db
Case Else
' MsgBox Err.Number & " " & Err.Description
End Select
End Function
********************************************************************************
That's the nearest I've got, but as you can see it was a non starter
September 21, 2005 at 7:07 am
The insert statement MUST look like this :
Insert into SomeTable (col1, col2...)
Select col1, col2... from YourOuterDataSource
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply