Re: ODBC Ops Nav to MS Access...

Peter -

I got your email...

I copy/paste your VBA into the Module of MS Access, change the variables.

Now I ran it and it bombed out with the following message:

"Run-Time error '3011':
the Microsoft jet database engine could not find the object 'APCREC'. Make sure the object exists and that you spell its name and the path name correctly.

I looked on the AS/400, the file is there and has many records...I do not understand the error at all. However, I clicked on the debug and it highlighted this portion of the VBA:

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=B2HDSDATA;UID=GIL;pwd=giln7924", acTable, _
rsAS400!MBFILE, rsAS400!MBFILE

One more thing, I realized in MS Access there is a size limit to only 2gb. I have approximately 20gb of DB files to download. Since MS Access can not accommodate, is it possible that the VBA can create individual mdb files for each database file transferred? Meaning, of the 300 files being downloaded, I will have 300 individual mdb files on the disk representing each database file accordingly.

Every, thank you for all your tips and help..today is Tuesday and it is do or die day...

Gil


Peter Kinsman wrote:
Here is the next instalment

Option Compare Database
Option Explicit

Public varReturn As Variant

Public Sub Copy_Files()
Dim cnAS400 As ADODB.Connection, rsAS400 As ADODB.Recordset
Set cnAS400 = New ADODB.Connection
varReturn = SysCmd(acSysCmdSetStatus, "Creating list of files in ")
cnAS400.Open "Provider=IBMDA400; data source=System_Name;", "User-ID", "Password"
cnAS400.Execute "{{QSYS/DSPFD FILE(" & Library_Name & "/*ALL) TYPE(*MBR) " & _
"OUTPUT(*OUTFILE) OUTFILE(QGPL/FILELIST)}}"
Set rsAS400 = cnAS400.Execute("SELECT MBFILE FROM QGPL.FILELIST " & _
"WHERE MBFLS = 'Y' AND MBFATR = 'PF' AND MBNRCD > 0")
If rsAS400.EOF Then
MsgBox "No files to copy"
Else
Do While Not rsAS400.EOF
varReturn = SysCmd(acSysCmdSetStatus, "Copying file " & rsAS400!MBFILE)
DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=Data_Source_Name;UID=User-ID;pwd=Password", acTable, _
rsAS400!MBFILE, rsAS400!MBFILE
rsAS400.MoveNext
Loop
End If
varReturn = SysCmd(acSysCmdClearStatus)
rsAS400.Close: Set rsAS400 = Nothing
cnAS400.Close: Set cnAS400 = Nothing
End Sub

I have created the list of files in library QGPL sothat it doesn't get confused with the library you are copying.
You will need to substitute:
System_Name
Library_Name
User-ID and Password in two places
Data_Source_Name pointing to the library you wish to copy.
You will need to check that the VBA has the nexessary references, but I did not need to add any, so the defaults should be sufficient.
I suggest that you single step to start with, but I have just imported 800Mb of files in one go.

Best of luck

Peter

"Peter Kinsman" <peter@xxxxxxxxxxxxxxxxxxxx> wrote in message news:ZJxij.190800$cJ3.172351@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
I think you will find that the field names imported into Access are the actual field names, unless there is an alias, in which case that is imported. When creating a DDS to map a flat file - several of my clients run in the System/36 environment - I sometimes create an alias from the field description.

Peter

"CRPence" <crpence@xxxxxxxxxxxx> wrote in message news:478a8dfc$1@xxxxxxxxxxx
The OpsNav database interface has a "Generate SQL DDL" function which will, IIRC, include the various LABEL ON statements in the script. There is an API for that function: QSQGNDDL

When no COLHDG is defined, the database exposes the short field name as the heading.

OT comment: FWiW it is often good to start a new thread, as the OP, for any additional inquiry that is essentially different than the current thread subject. Whether the difference justifies a new thread is up to the OP. It is difficult for a responder to justify starting a new thread, even possibly considered improper netiquette, since a new thread may not even be seen by the OP, if the OP is only tracking their own thread. I do however sometimes change the subject within a thread, to clarify that a change in direction of conversation from the original topic. The current subject is pretty generic; i.e. not specific enough that someone in the future finding it from a keyword search, would know what the discussion thread really entailed. Specific and separate threads allow someone to better find what they are searching for, just by perusing the titles\subjects in a search; e.g. a google groups search.

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind whatsoever and may not represent positions, strategies, nor views of my employer

Gil wrote:
<<SNIP>>

I know the answer of this next question but just to remove any doubts from professionals here.

Is there a way [to] not only import DB2 files into Microsoft Access
that retains all database fields properties, but also the fields' descriptions? I know some DB2 files are 'externally described'. But I've been told by our vendor that these files are not externally described. So it seems that all Physical files data fields are using the COLHDG code. Can those field description be populated into the MS Access? - or should I start a different thread asking the same thing.



.

Relevant Pages

  • Re: Merging content from access
    ... My hunch at the moment is that this is a problem in my access query. ... I agree with you that Word VBA is a more elegent solution. ... The other field-based technique requires you to have a "parent" table as ... the mail merge data source and a child table inserted using a DATABASE ...
    (microsoft.public.word.docmanagement)
  • Re: Possible Project Bug: All Projects in Database are Deleted upon Save
    ... best practices and Project VBA ... > Only Project1 is saved into the database. ... >> prompts with Append or overwrite message. ... >> For Microsoft Project companion projects, ...
    (microsoft.public.project.vba)
  • Re: ODBC Ops Nav to MS Access...
    ... You will see that there is a new object dbsNew and ... database, copy the table into the new database and delete the table from the ... varReturn = SysCmd ... I copy/paste your VBA into the Module of MS Access, ...
    (comp.sys.ibm.as400.misc)
  • Re: What happens when data in control is updated
    ... When I developed my concept database file I tended to look at what ... changes expected appearing on the form itself) and not going through the VBA ... > if the primary key field is Text data type, rather than numeric, the ... >>> make sure that a requery is necessary to achieve whatever your aim is. ...
    (microsoft.public.access.formscoding)
  • Re: Moving to SharePoint - Advice?
    ... if you split your database then the front end cannot call those stored procedures. ... However the back end table triggers can of course can call those store procedures that exist in the back end. ... So if your database is un-split, then VBA, or Macro code etc. can call store procedures and values can be returned back. ... just front end tables that are synchonrized periodically with SharePoint, ...
    (comp.databases.ms-access)