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
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: