Copy data from one database to another ADOX

[sisva18]

Hey I was wondering about how you would move tables from one db to another.

So far I have defined the function TransferData and I have defined the sConn from my source db to give to cnn and set sourceCat with it.
When I look in the debugger I can see that the tables are showing and the connection is right.

The problem is with my targetCat where I have tried giving it the currentproject.connection as active connection. But that wont do it..
Then I tried defining a sConnOne which is the other db connection string then i created another adodb connection cnnOne to give it the sConnOne connection.
I then looked in the debugger and saw that it was indeed the right tables it was showing from sConnOne.
But then when I ran the code TransferData("TableTest") I get a errorcode 3251.

Function TransferData(tableName As String)

Dim cnn As New ADODB.Connection
Dim cnnOne As New ADODB.Connection
Dim sourceCat As New ADOX.Catalog
Dim targetCat As New ADOX.Catalog
Dim sConn As String
Dim sConnOne As String

sConn = "Driver={SQL Server};" & _
"SERVER=SRV-SQL-TEST;" & _
"Trusted_Connection=Yes;" & _
"DATABASE=" & getDatabaseNameDev

sConnOne = "Driver={SQL Server};" & _
"SERVER=SRV-SQL-TEST;" & _
"Trusted_Connection=Yes;" & _
"DATABASE=" & getDatabaseName


cnn.Open sConn
Set sourceCat.ActiveConnection = cnn

'cnnOne.Open sConnOne
Set targetCat.ActiveConnection = CurrentProject.Connection
'cnnOne



Dim sourceTable As ADOX.Table
Set sourceTable = sourceCat.Tables(tableName)

Dim newTable As New ADOX.Table
Set newTable.ParentCatalog = targetCat
newTable.name = sourceTable.name

Dim sourceCol As ADOX.Column
Dim newCol As ADOX.Column

For Each sourceCol In sourceTable.Columns
Set newCol = New ADOX.Column
newCol.name = sourceCol.name
'newCol.Type = sourceCol.Type
newCol.DefinedSize = sourceCol.DefinedSize
newCol.ParentCatalog = targetCat

newTable.Columns.Append newCol
Next sourceCol

targetCat.Tables.Append newTable

End Function

[Micron]

Do you know that you can just open the target db and select New Data Source on External Data tab, choose From Database > Access and navigate to the db containing the tables you want and import them? IIRC, you can save that as an import spec.

[sisva18]

Do you know that you can just open the target db and select New Data Source on External Data tab, choose From Database > Access and navigate to the db containing the tables you want and import them? IIRC, you can save that as an import spec.

Yes I know I can but the problem is that I have a developement database and a production database. I want to commit the changes from developement database to production database so I need it to be in code.

[Micron]

Sorry, I still am not on the same page. Sounds like your db is not split, which would be a mistake. Any development db I worked on only contained the modules, forms, reports and queries. When that was released (or re-released) only the fe was distributed and the links altered to connect to the production data in the back end tables. So no tables ever needed to be copied anywhere. I'm afraid I don't see the point of all that code to do what you can do with the ribbon regardless of the reason why it needs to be done. Best I let someone with more ADO experience help you as I have rarely needed ADO as opposed to DAO.

[June7]

Must be split if trying to connect to SQLServer.

However, I also don't understand the situation.