Importing ODBC tables into a database 1

[xsnrg]

I am looking for the code that would allow me to automate importing ODBC tables from a password protected SQL server into my current or an external Access database.

Anyone have ideas to leads on this?

 

[PHV]

Have you tried to create a system DSN with the correct credentials ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xsnrg]

I do have an DNS and use that DNS when I import manually. But I don't know the code to call a specific table in that DNS and import it into the current Access database.
I've tried the macro approach but even that is not working. any help?

 

[xsnrg]

OK, I figured out how to do this but have another question.
Can I have a module code in one database that can use this process to import an ODBC SQL table but make the destination be a table in another database?

 

[cmmrfrds]

There are probably multiple ways. Outline specifically what tables/queries that you need to move data between. What databases etc... How often? On demand or by schedule? What versions of the different databases.

 

[xsnrg]

Quick background...
I am importing large SQL tables into 5 separate backend Access files as my support data.

I have automated each of the 5 databases that just opening them will pull the newest SQL data.

On the main frontend database, I'd like to have code that, when a button is clicked, these other 5 databases will open one at a time and run their respective code and therefore update my backend data.

So, I'm just need code that will allow me to open another external database without closing the current one.

 

[cmmrfrds]

How versed are you in vba coding? This can be done through ADO but will require some coding.

 

[xsnrg]

I've written quit a bit of code and borrowed and tweaked even more. I'm willing to try anything... if you have a resourse of an example to point me to, I'll check it out.

Thank you

 

[lewds]

Hi..

Seems complicated.

A possible solution is to..

1. create links to all the SQL tables you want
(with the aforemention DSN)
2. create make table query to do a temp table on your
frontend for all the data you want from the SQL tables
(you may or may not want to check over the data)
3. create links to all the backend tables
(with newly created DSN's for each database)
4. create queries to update the data in your 5 backends
from the newly created temp table
( no need to open the other DB's at all)
5. delete temp table in preparation for next update

 

[cmmrfrds]

Let's start out by getting a simple connection to your sql server database working. Create a public function in the standard module (code below) and then create a macro to run the code. In the macro do "runcode" and then put in the function name.


Public Function ReturnUserRemote() As String
Dim cn As New adodb.Connection, sql1 As String
Dim rs As New adodb.Recordset, connString As String

connString = "provider=SQLOLEDB.1;" & _
"User ID=yourid;" & _
"Initial Catalog=ReportInventory;" & _
"Data Source=Boxer;" & _
"Password=yourpassw; & _
"Persist Security Info=True"
cn.ConnectionString = connString
cn.Open connString

sql1 = "Select suser_sname() "
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

ReturnUserRemote = rs(0)
Debug.Print "user name = "; rs(0)

rs.Close
Set rs = Nothing
End Function

 

[xsnrg]

Lewds,
I actually tried that first but it was still going after 30 minutes so I gave up on this as a solution.

I did find a solution on Microsoft's newsgroups but still it's only part of the problem...

Private Sub Command1_Click()
Shell """C:\Program Files\Office2K2\Office10\MSAccess.exe"" " & _
"""D:\AccessTests\A2K2_MyTest\Test01.mdb""", vbNormalNoFocus
End Sub

But since I'm going to do this for 5 databases, and each has an import process to perform, this practically kills the network if they all run at the same time...

How can I pause this between each database opening, running the import process and then quiting?

 

[cmmrfrds]

Linked tables is very inefficient as you have discovered. I was planning on showing you how to do this through sql server. If you are still interested let me know but first work through the example I gave earlier.

 

[xsnrg]

cmmrfrds -
That does sound like a better option.
I tried the code you gave and got a Compile error on the section starting with connString.
For some reason, that variable is not getting declared. Any guesses as to why?

 

[cmmrfrds]

Maybe it is flagging the recordset. Make sure you have a reference set to the ado library - probably version 2.7. I will look at this later tonight.

'-- Reference Library
'-- Microsoft ActiveX data objects 2.6 library needed for ADO

 

[xsnrg]

I did check that library 2.6 and added it. But still no help...
See this image for what libraries I have...

http://buddycenters.org/images/reflib.jpg

 

[cmmrfrds]

This line has a syntax error.

"Password=yourpassw; & _
should be
"Password=yourpassw;" & _

When I replaced the password I wiped out the double quote.

 

[cmmrfrds]

There are a couple of ways to do this by letting sql server do the work. One way is to have your DBA make a "linked server" on sql server for each of your Access mdb tables. So, that would be 5 linked servers on your sql server database. The advantage is that you can treat the access tables just like any other sql server table. For example, you could send over the ADO connection the following SQL.

INSERT INTO MyAccessTable (PriKey, Description)
SELECT ForeignKey, Description
FROM MySQLTable

The disadvantage is that the DBA needs to do the linked servers for you. The DBA may not want to do it since they will need to maintain - should be minimal.

The other way to do it is by sending the SQL statement to sql server with the information on the Access table. Sql server has a Function called OPENROWSET that will run a query that crosses servers (including ADO connections).

Here is an example of what that could look like.
Dim sqlString As String
sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
'--- Define the location and database name
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

The above is joining the orders table in an access mdb to the customers table in the sql server database northwind.

The advantage here is that the DBA does not need to do anything. Of course, your query would be an insert into statement. I suggest you get the first simple sql statement to work on the ADO connection and build up from that point.

 

[cmmrfrds]

A third option on sql server is to use DTS and this is probably the easiest for you, but requires setup by the DBA. The DBA can setup a DTS Package for each mdb table that needs to be loaded on a scheduled basis. The advantage is that sql server has a scheduler so these packages could be scheduled to run on a periodic basis. The DBA could condition these packages such that you could be emailed on the success of the package. There is some work in setting up email with sql server so the email may not be available.

Another option, is to connect both data sources on the client side through ADO connections. This would be less efficient than the first 3 options since it would require much more network traffic. This would be the least desireable if for some reason the first 3 options cannot be used.