Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with ADODB connections.

Status
Not open for further replies.
Sorwen

Sorwen

Technical User
Nov 30, 2002
1,641
US
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:
Code: 
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:

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?
 
Sort by date Sort by votes
I think that this may be due to only having Access to test on at my end. This is a guess:

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;"

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

rs.Open sqlSQL, cn, adOpenForwardOnly, adLockReadOnly

End Function
 
Upvote 0 Downvote
I now get an new error. Which is strange as I didn't get it on cmmrfrds code which used the same provider.

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

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Click to expand...

I'll have to try his code again and see if I get the same error now.
 
Upvote 0 Downvote
Here is another guess.
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 = "SELECT * INTO TestTable IN " & _
"'" & CurrentProject.FullName & "'" & _
" FROM REMOTE_TEST;"

rsTable.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
 
Upvote 0 Downvote
cmmrfrds. The original time I tested was the read only error and now it is the SQL Server does not exist. They been doing a lot of changes with our network this week so it is possible something has changed since the first time I ran it.

Well Remou we get a new error:
Run-time error '-2147217900 (80040e14)':

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token TESTTABLE was not valid. Valid tokens: :.
Click to expand...
I got this before when I first tried doing this months ago. I think that was why I went to the provider of "Microsoft.Jet.OLEDB.4.0".
 
Upvote 0 Downvote
Sorwen, what can you connect with? (I took the last connection string from you original post.) It may be that if you get this sorted out, you will find that cmmrfrds suggestions works. [ponder]
 
Upvote 0 Downvote
Sorry your are right. To connect to a remote table I seem to have to use the .Provider = "MSDASQL" .ConnectionString = "DSN=DSNREMOTE;". I use that right now to dump data record by record into a local table. Because of the number of records this can take hours which makes it impractical for most uses. To connect to a local table I seem to have to use "Microsoft.Jet.OLEDB.4.0". I did switch to the "Microsoft.Jet.OLEDB.4.0", but that was when I could use linked tables. The jist I got of the ODBC error before was that it came up because it was trying to write the table to the remote database, which I do not have permissions for. I could be wrong about what it was/is trying to do though.

Here is the code I use to create and dump data into a local table. Maybe there is a faster way than what I'm doing and that would work at least. It isn't pretty, but gets the job done till I can go back through and maybe clean up more.


Code: 
Function CreateLocalTable(LocalTableName As String, RemoteTableName As String, Optional qrySQL As String, Optional AppendTable As Boolean, Optional ProgressMessage As String)
Dim Con As ADODB.Connection
Dim Con2 As ADODB.Connection
Dim rsTable As ADODB.Recordset
Dim rsTable2 As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String
Dim FieldList As String
Dim Count As Long
Dim Count2 As Long
Dim StartCount As Long
Dim IsTrueNumber As Long 'Because they use # in field names and should be shot
Dim IsTrueDollar As Long 'Because they use $ in field names and should be shot
Dim checktype As String
Dim TotalCount As String
Dim CurrentCount As String
Dim rcRecord As Variant

Set Con = New ADODB.Connection
Set Con2 = CurrentProject.Connection
Set rsTable = New ADODB.Recordset
Set rsTable2 = New ADODB.Recordset

With Con
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=REMOTETABLE;"
    .Open strConnect, "UserName", ODBCPassword("UserName") 'Search Access table for password to use
End With

If qrySQL = "" Then
    strSQL = ""
    strSQL = strSQL & "Select *"
    strSQL = strSQL & " From " & RemoteTableName
Else
    strSQL = qrySQL
End If
    
rsTable.Open strSQL, Con, adOpenDynamic, adLockOptimistic

Count2 = 0

With rsTable
    If AppendTable = False Then
        If CheckTable(LocalTableName) = True Then
            DoCmd.DeleteObject acTable, LocalTableName
        End If
        
        For Count = 0 To (.Fields.Count - 1)
            IsTrueNumber = InStr(1, .Fields.Item(Count).Name, "#")
            IsTrueDollar = InStr(1, .Fields.Item(Count).Name, "$")
            
            If IsTrueNumber > 0 Or IsTrueDollar > 0 Then
                FieldList = FieldList & "[" & .Fields.Item(Count).Name & "] " & _
                                    ConvertOLEDBType(.Fields.Item(Count).Type)
           Else
                FieldList = FieldList & .Fields.Item(Count).Name & " " & _
                                    ConvertOLEDBType(.Fields.Item(Count).Type)
            End If
            If Count < (.Fields.Count - 1) Then
                FieldList = FieldList & ", "
            End If
            Count2 = Count2 + 1
        Next Count
    
        strSQL = ""
        strSQL = strSQL & "Create Table " & LocalTableName & " (" & FieldList & ");"
        
        Con2.Execute strSQL
    End If
    
    strSQL = ""
    strSQL = strSQL & "Select * From " & LocalTableName & ";"
    rsTable2.Open strSQL, Con2, adOpenDynamic, adLockOptimistic
    
    DoCmd.OpenForm "progress_frm"
    CurrentCount = 0
    If ProgressMessage <> "" Then Form_progress_frm.progress_msg.Caption = ProgressMessage
        
    Do While .EOF <> True
        Count2 = 0
        rsTable2.AddNew
        For Count = 0 To (.Fields.Count - 1)
            rsTable2.Fields.Item(Count2).Value = .Fields.Item(Count).Value
            Count2 = Count2 + 1
        Next Count
        .MoveNext
        CurrentCount = CurrentCount + 1
        Form_progress_frm.progress_lbl.Caption = CurrentCount
            DoEvents
    Loop
    .Close
    DoCmd.Close acForm, "progress_frm", acSaveNo
End With


End Function


Function ConvertOLEDBType(DataType As Long) As String
Dim strType As String

Select Case DataType
    'Case AdArray
        'strType = "Array"
    Case adBigInt
        strType = "Number"
    Case adBinary
        strType = "Number"
    Case adBoolean
        strType = "YesNo"
    Case adBSTR 'May get tricky as max text is 255
        strType = "Text(255)"
    Case adChapter
        'Not in use
        strType = "Memo"
    Case adChar
        strType = "Text(255)"
    Case adCurrency
        strType = "Currency"
    Case adDate 'Not standard date format
        strType = "Number"
    Case adDBDate 'standard date format
        strType = "DateTime"
    Case adDBTime
        strType = "DateTime"
    Case adDBTimeStamp
        strType = "DateTime"
    Case adDecimal
        strType = "Number"
    Case adDouble
        strType = "Number"
    Case adEmpty
        'Not in use
        strType = "Memo"
    Case adError
        'Not in use
        strType = "Memo"
    Case adFileTime
        strType = "Number"
    Case adGUID
        strType = "Number"
    Case adIDispatch
        'Not in use
        strType = "Memo"
    Case adInteger
        strType = "Number"
    Case adIUnknown
        'Not in use
        strType = "Memo"
    Case adLongVarBinary
        strType = "Number"
    Case adLongVarChar
        strType = "Text(255)"
    Case adLongVarWChar
        strType = "Text(255)"
    Case adNumeric
        strType = "Number"
    Case adPropVariant
        'Not in use
        strType = "Memo"
    Case adSingle
        strType = "Number"
    Case adSmallInt
        strType = "Number"
    Case adTinyInt
        strType = "Number"
    Case adUnsignedBigInt
        strType = "Number"
    Case adUnsignedInt
        strType = "Number"
    Case adUnsignedSmallInt
        strType = "Number"
    Case adUnsignedTinyInt
        strType = "Number"
    Case adUserDefined
        'Not in use
        strType = "Memo"
    Case adVarBinary
        strType = "YesNo"
    Case adVarChar
        strType = "Text(255)"
    Case adVariant
        'Not in use
        strType = "Memo"
    Case adVarNumeric
        strType = "Number"
    Case adVarWChar
        strType = "Text(255)"
    Case adWChar
        strType = "Text(255)"
    Case Else
        strType = "Memo"
End Select

ConvertOLEDBType = strType

End Function
 
Upvote 0 Downvote
My original connection string.
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

My sql server is running on my PC therefore the sql server name is localhost

Did you change the name to your sql server name?

"Data Source=yoursqlservername;" & _

 
Upvote 0 Downvote
I did it both ways. I wasn't sure on the localhost (I knew it meant my computer, but not why it was used) so I tried it with and with the remote database. They have been restricting more and more lately so that some things that worked yesterday (or a couple days ago) will not work today. :(


Hence the problem to start with. All of this started because they've restricted the password used for the remote server to only certain users and it also now has to be entered anytime the connect is read from. And I can't really run to every one's computer each time. ;) That's why I'm so grateful for your and everyone else's help.
 
Upvote 0 Downvote
Your connection string.
With Con
.Provider = "MSDASQL"
.ConnectionString = "DSN=REMOTETABLE;"
.Open strConnect, "UserName", ODBCPassword("UserName") 'Search Access table for password to use

The provider MSDASQL uses odbc to be backward compatible, it is better to use the provider SQLOLEDB.1 which is a natvie OLEDB provider. There is an extra odbc layer in your provider and this can be especially confusing on errors since the error needs to be passed to each layer and can be reinterpreted.

Also, there can be multiple errors, so it is a good idea to put in an error handler to see all the underlying errors, not just the error the ODBC library decided to give you.

Example.
Public Function SomeADOFunction()
On Error GoTo Errhandler

Your code here

Exit Function
Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function
 
Upvote 0 Downvote
Thanks. I used that, but the only errors where the ones I gave. With the additional information of the error source. Which was "Microsoft OLE DB Provider for SQL Server" for SQLOLEDB.1 and "Microsoft OLE DB Provider for ODBC Drivers" for the invalid token error.
 
Upvote 0 Downvote
Can you paste in the error along with your connection information.
 
Upvote 0 Downvote
Code: 
connString = "provider=SQLOLEDB.1;" & _
            "User ID=AUserID;Password=APassword;" & _
            "Data Source=DSNName;" & _
            "Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
Gets
In Error Handler [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
-2147467259
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Microsoft OLE DB Provider for SQL Server
Click to expand...

Code: 
With Conn
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=DSNName;"
    .Open , "AUserID", "APassword"
End With

strSQL = "SELECT * INTO TestTable_tbl IN " & _
"'" & CurrentProject.FullName & "'" & _
" FROM RemoteTableName;"

rsTable.Open strSQL, Conn, adOpenDynamic, adLockOptimistic
Gets
In Error Handler [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token TESTTABLE_TBL was not valid. Valid tokens: :.
-2147217900
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token TESTTABLE_TBL was not valid. Valid tokens: :.
Microsoft OLE DB Provider for ODBC Drivers
Click to expand...
 
Upvote 0 Downvote


Get rid of the DSN and try directly to the server.

connString = "provider=SQLOLEDB.1;" & _
"User ID=AUserID;Password=APassword;" & _
"Data Source=yoursqlservername;" & _
"Initial Catalog=yourdatabasename;" & _
"Persist Security Info=False"

 
Upvote 0 Downvote
I not sure if I'm understanding you right, but as far as I'm aware I cannot do that. The DSN (ODBC Connection) is the only way it knows where the data is. It isn't on a table on a network drive that I can just give it a path to. For the purposes of access the ODBC Connection is my only connection to the data.
 
Upvote 0 Downvote
For some reason I thought the remote connection was to an SQL Server. What kind of database are you connecting to? DB2, Oracle, SQL Server, other?

If it is an sql server database, then go into the ODBC setup and see what server and database is being used.

 
Upvote 0 Downvote
Looking back I never did say exactly what my setup was, just that I had to pull through odbc. Which would have been helpful. Sorry. The tables are on a remote AS400 machine. There is some type of SQL sever/interface/something in the background, but I do not have permissions to allow it to run the queries remotely (which would be nice). I'm allowed to link directly to the all/any table(s) through a read only ODBC connection.
 
Upvote 0 Downvote
I am sorry, I should have asked sooner. I was working from the assumption it was sql server, which is easier to work with in the Microsoft environment. I don't use as400 tables, but can you link the as400 table under File, Get External Data.

Probably better to start a new thread with the work as400 in the title. So, that you get the as400 experts to look at it.
 
Upvote 0 Downvote
That goes back to the problem that I can't use linked tables. I have to pass the username and password each time I read from the table and I can only do that from vba code. I wish I could just link them again as it would make life much easier. I'll take your advice and once this thread gets buried make one with as400 in the title. Thanks for all your help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

M
  • Locked
  • Question
display a calendar with appointments in body of html email with access vba
Replies
1
Views
3K
SoftwareRT
SoftwareRT
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
4K
Norman Sherlock
Norman Sherlock
Dan Stoica
  • Locked
  • Question
Problems with Foxpro dsnless linked tables
Replies
0
Views
373
Dan Stoica
Dan Stoica
JaineyBlue
  • Locked
  • Question
DLookup with criteria 1
Replies
2
Views
340
JaineyBlue
JaineyBlue
TheAceMan1
  • Locked
  • Question
Delete from listi n XL open tab with VBA
Replies
2
Views
387
TheAceMan1
TheAceMan1

Part and Inventory Search

Sponsor

Back
Top