DSN less ODBC Connection iSeries (1 Viewer)

MHutcheson

Registered User.
Local time
Today, 10:01
Joined
Sep 3, 2013
Messages
23
Hi all,

I've hit a bit of a wall with a current project. I have a Access 2010 32-bit database that is linked via ODBC to an IBM iSeries. This application runs various checks and needs to update a table. It works fine when I use it on my PC with a System DSN connection. However, this is going to be deployed to a user and I have instructions to hard code in VBA the connection.

My db as 6 tables and so far I have the following in an AutoExec module:

(I've X'd out any sensitive info)

==================================

Option Compare Database
Option Explicit
Type TableDetails
TableName As String
SourceTableName As String
Attributes As Long
IndexSQL As String
Description As Variant
End Type


Public Function IBM()
Dim cn As New ADODB.Connection
Dim MinConStr As String
Dim S As String
Dim uid As String
Dim pwd As String

'You can use a full or partial connect string.

'Minimum connect string requires DRIVER and SYSTEM keywords.
'All other values default to the same value as a new data source.
'MinConStr = "DRIVER={iSeries Access ODBC Driver};SYSTEM=X.X.X.X;"
'cn.Open MinConStr
'cn.Close

'Full connect string
'NOTE: Connect strings should be verified after each CA version upgrade
S = "DRIVER=iSeries Access ODBC Driver;"
S = S & "MGDSN = 0;"
S = S & "SEARCHPATTERN = 1;"
S = S & "ALLOWUNSCHAR = 0;"
S = S & "COMPRESSION = 0;"
S = S & "MAXFIELDLEN = 32;"
S = S & "SIGNON = 3;"
S = S & "SSL = 2;"
S = S & "SORTWEIGHT = 0;"
S = S & "LANGUAGEID = ENU;"
S = S & "DFTPKGLIB = QGPL;"
S = S & "PREFETCH = 0;"
S = S & "SORTTYPE = 0;"
S = S & "CONNTYPE = 0;"
S = S & "REMARKS = 0;"
S = S & "LIBVIEW = 0;"
S = S & "LAZYCLOSE = 1;"
S = S & "TRANSLATE = 0;"
S = S & "SCROLLABLE = 0;"
S = S & "BLOCKSIZE = 32;"
S = S & "RECBLOCK = 2;"
S = S & "XDYNAMIC = 1;"
S = S & "DEC = 0;"
S = S & "TSP = 0;"
S = S & "TFT = 0;"
S = S & "DSP = 1;"
S = S & "DFT = 5;"
S = S & "NAM = 0;"
S = S & "DBQ = DDILLING;"
S = S & "CMT = 0;"
S = S & "System = x.x.x.x;"

'UID and PWD can be passed in the connect string or on the open. This example passes them on the open.
S = S & "UID=xxxxxx;" 'optional
S = S & "PWD=xxxxxx;" 'optional

cn.Open S, uid, pwd
'cn.Close

'Or build and distribute a file dsn with the application. Use ODBC administrator to create the file dsn text file.
'cn.Open "File Name=C:\mydsn.dsn"
'cn.Close


'Another option is to build the datasource in your install
' - Use the ODBC Installer C APIs. VB Example (mnuCreateDSN_Click) is shown below.
' - Use the DAO DBEngine's RegisterDatabase method
' - Create the registry key (not recommended)

For Each varLinkedTableName In colCurrentTables
DoCmd.TransferDatabase acLink, "ODBC Database"
strConnectionString , acTable, varLinkedTableName, varLinkedTableName
Next

End Function

==================================

When I compile though I receive the message

Variable not defined

And varLinkedTableName is highlighted in the for each statement. Does this mean a reference is missing?

Also, I'm not even sure if the above is the correct way to achieve what I want? Is it best to link the tables first using the system DSN then use VBA to refresh them manually?

Any help would be greatly appreciated.

Regards,

Michael
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 10:01
Joined
Feb 19, 2013
Messages
16,553
you are using option explicit, so you must declare all variables - so far as I can see you haven't declared varLinkedTableName - or colCurrentTables
for that matter
 

Users who are viewing this thread

Top Bottom