Need help with ADODB connections.

[Sorwen]

How can I pull data from an ODBC Connection, but write to a table in my access database? I use to be able to setup a link under tables to the remote file and then just allow access to handle pulling the data and since the connection is using the current database it just writes the table in the session.

Example:

Dim Conn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim strSQL As String

Set Conn = CurrentProject.Connection
Set rsTable = New ADODB.Recordset

strSQL = ""
strSQL = strSQL & "Select *"
strSQL = strSQL & " Into TempTable_tbl" 'Table in Current Access Database"
strSQL = strSQL & " From RemoteTable;"

rsTable.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
....

I would then do whatever I needed. The problem now is that I cannot preset the link in Access. I can connect with the following code:

Dim Conn As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim strSQL As String

Set Conn = New ADODB.Connection
Set rsTable = New ADODB.Recordset

With Conn
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=DSNNAME;"
    .Open , MyUser, MyPassword
End With

strSQL = ""
strSQL = strSQL & "Select *"
strSQL = strSQL & " From RemoteTable;"

rsTable.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
...

Since the connection is to the remote data if I do an Insert it is going to try to create the table there and I do not have access to do that. I could set all the data to variables and then set those variables to a table in this database, but I would rather not if I can avoid it. Can any one help?

 

[Remou]

Would GetRows work for you?

<...>
arsTable = rsTable.GetRows
intRecFirst = LBound(arsTable, 2)
intRecLast = UBound(arsTable, 2)
intFieldFirst = LBound(arsTable, 1)
intFieldLast = UBound(arsTable, 1)

Set rsTable2 = New ADODB.Recordset

rsTable2.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

For i = intRecFirst To intRecLast
    rsTable2.AddNew
    For j = intFieldFirst To intFieldLast
        rsTable2.Fields(j) = arsTable(j, i)
    Next
    rsTable2.Update
Next
<...>

 

[Zion7]

or, along the same lines as Remou pointed out.
You can still use INSERT, because the only purpose of the remote Connection, is to get the DATA, once here, Insert...

...
rsTable.Open strSQL, Conn, adOpenDynamic, adLockOptimistic

Do Until rsTable.EOF
CurrentProject.Connection.Execute _
"INSERT INTO LocalTable(txtDay,txtName,txtCity) " & _
"VALUES(" & rsTable!txtDay & ",'" & rsTable!txtName & "','" & _
rsTable!txtCity & "')"

rsTable.MoveNext
Loop

 

[cmmrfrds]

Another way is to let SQL Server do the insert into the Access MDB table. There is a function in sql server called OPENROWSET that provided you give it the connection information sql server can write to the access table.

Here is an example. I have sql server running on my PC so that I can reference the C: drive in a remote case you would need to use the UNC name. One caveat for testing is that the MDB you have open for design will be in exclusive mode. So, create another mdb for the testing. This example has a table in employee2.mdb called newshippers and has columns companyname and phone.

Public Function rowsetInsert()
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlstring As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlstring = "insert Into "
sqlstring = sqlstring & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlstring = sqlstring & "'C:\aemptydir\employee2.mdb';'admin';, NewShippers) "
sqlstring = sqlstring & "(CompanyName, Phone) "
sqlstring = sqlstring & "Select CompanyName, Phone From Shippers"

rs.Open sqlstring, cn, adOpenForwardOnly, adLockReadOnly

End Function

 

[Sorwen]

Thanks for the help everyone.

Remou, that is what I figured I would have to do. I'm going to have to play with putting the data in there might not be much difference, but for some things I've found looping can be a lot slower and I hoped to avoid that as sometimes I'm dealing with a lot of records.

Zion7, that looks like what I might need. I'll have to look into that LocalTable.

cmmrfrds, that looks great. I don't think I shouldn't have enough access on the back end for it to create a connection like that. I'll have to play and see because what little I do know about creating the connection has been mostly self taught guess work.

 

[Sorwen]

Zion7 I think I must have misunderstood what you were saying. When you said LocalTable you didn't mean it was a command, but the table name? If that is the case then that doesn't work as I had already tried that and it doesn't try to create it locally, but it tries to create it on the remote system.

I say all that and I just reran the code to get the error to explain and it just worked. WTF? Since when had programming become like baking and I just have to let the recipe set for a day?

 

[Sorwen]

Zion7 I think I must have misunderstood what you were saying. When you said LocalTable you didn't mean it was a command, but the table name? If that is the case then that doesn't work as I had already tried that and it doesn't try to create it locally, but it tries to create it on the remote system.

No this is true. Someone forgot to save and it was running the old code.

 

[Zion7]

I'm not sure why, it won't work?
Just opening a remote recordset, gathering the data,
and imputting into a local table, is very straightforward.
Is your "local" table, actually a linked table?

This is feasible from a remote ACCESS Dbase,
into a local Access Dbase.

Not sure if possible using SQL Server??

    Dim strSQL As String, strResult As String
    Dim strExtDBase As String

    strExtDBase = "C:\Documents and Settings\Dan\My Documents\" & _
                    "BackUp\DBase\AddIns\AddIn.mdb"

            strSQL = "INSERT INTO tblSupplier(txtSupplier) " & _
                "SELECT txtSupplier " & _
                "From tblSupplier " & _
                "IN '" & strExtDBase & "'"
            
            
            CurrentProject.Connection.Execute strSQL, , 129

 

[Sorwen]

No the table is local to the database. I'll have to do it for the error again, but It doesn't find the local table and instead tries to create a table on the remote server.

 

[Sorwen]

I might be misunderstanding. The message is:

Run-time error '-2147217900 (80040e14)':

[IBM][iSeries Access ODBC Driver]Statement violates access rule:
Connection is set to read only.

Which is correct as I have read only access to the remote server. The Access database I'm accessing the data from is on my computer of which I have permissions to write to.

 

[Sorwen]

I going to assume there is no way to do this. Is there a way to pass all the data without the connection from one object to another? I know that an empty object can get all the data, but could a pre-set object get data from one to the other. I guess I'm just going to have to break down and do a loop.

 

[Sorwen]

If any one has ideas I would be greatful. It takes a long time to load 120k records with loops.

 

[Remou]

There may be the start of an idea here. I can only test with two Access databases, but this is what I tried:

strsql = "SELECT Audit.* INTO Audit2 IN " _
& "'C:\tek-tips1.mdb' " _
& "FROM Audit;"

Set rsTable = New ADODB.Recordset
objCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;Data Source=C:\Tek-Tips2.mdb"

rsTable.Open strsql, objCon, adOpenDynamic, adLockOptimistic

 

[cmmrfrds]

Do you have any client access to the sql server besides Acess? Your DBA could setup a DTS package to load the table on the PC. This is not a hard task for the DBA. It is more of a permissions/availability thing.

Is the sql server on your LAN? If so, did you try the OPENROWSET?

 

[Zion7]

Sorwen, would not what either Remou & I showed you, work?
Look at the SQL syntax, the "IN" statement allows
you to refer to Remote DBases.

But, as we both stipulated, our examples
are based on 2 ACCESS DBases.

 

[Remou]

Sorry Zion7, I did not read your post properly.

 

[Zion7]

...No worries!

 

[Sorwen]

No, thank you though. Every time I try to do an INTO I get that runtime error. I don't know if it is due to it trying to write on the remote machine or that it is trying to use the ODBC driver to write to the local machine (which would be strange). I tried yours and cmmrfrds with the same error.

Run-time error '-2147217900 (80040e14)':

[IBM][iSeries Access ODBC Driver]Statement violates access rule:
Connection is set to read only.

and Remou's last one gets

Run-time error '-2147467259 (80004005)':

The database has been placed in a state by user 'Admin' on machine 'BOB' that prevents it from being opened or locked.

I assume because the code is running in the database that it needs to write to.

 

[Remou]

Have you saved everything? I usually get such an error if modules etc are not saved. So in a way it is due to running in the same database.

 

[Sorwen]

Yes that was the problem on that error. Now I get a new one.

Here is how I had to modify your code to work for me as a test for the final code:

Function testThis()
Dim objCon As Variant
Dim rsTable As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * INTO TestTable IN " & _
"'" & CurrentProject.FullName & "'" & _
" FROM REMOTE_TEST;"

Set rsTable = New ADODB.Recordset
objCon = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=UserName;Password=Password;Data Source=" & CurrentProject.FullName

rsTable.Open strSQL, objCon, adOpenDynamic, adLockOptimistic

End Function

And here is the new error:

Run-time error '-2147217843 (80040e4d)':

Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Thank you and everyone for your help.