Help with extracting data from fields use VBA

[JohnLee]

Hi Folks,

I have a table which has a number fields in it of which there are 4 fields that I want to extract the contents from.

My table is named - tblStdD&GNewBrandAdd and the fields that I want to target are:

strFormCode
strPlanNumber
strBatchID
strImagePath

I've done a bit of searching but haven't found anything that gives me sufficient information on how to assign the contents of those fields to a variable in my Module that I'm creating.

I believe I need to start with something like this

[COLOR=blue]Dim[/COLOR][COLOR=black] db [/COLOR][COLOR=blue]As[/COLOR][COLOR=black] DAO.Database[/COLOR][COLOR=blue]Dim[/COLOR][COLOR=black] tdf [/COLOR][COLOR=blue]As[/COLOR][COLOR=black] DAO.TableDef[/COLOR][COLOR=blue]Dim[/COLOR][COLOR=black] fld [/COLOR][COLOR=blue]As[/COLOR][COLOR=black] DAO.Field[/COLOR][COLOR=#008000]'Initialize the table.[/COLOR][COLOR=blue]Set[/COLOR][COLOR=black] db = CurrentDb()[/COLOR][COLOR=blue]Set[/COLOR][COLOR=black] tdf = db.CreateTableDef("tblStdD&GNewBrandAdd")[/COLOR]

but I'm struggle to work out how to get the above fields.

Any pointers, or sample code would be most appreciated.

Thanks

John

 

[tehNellie]

First off, the "&" in your table name is going to come and haunt you at some point.

You might find it easier to use a recordset rather than try and manipulate the table directly.

dim db as DAO.database
dim rs as DAO.recordset

  set db = currentdb

  'assuming an single record in the table only, if not use a query 
  set rs = db.openrecordset("tblStdD&GNewBrandAdd")

  'making sure there's actually a record in our recordset
  if not rs.EOF then
    
    strFormCode = rs![formcode]
    strPlanNumber = rs![Plannumber]
    strBatchID = rs![BatchID]
    strImagePath = rs![ImagePath]
 
 end if

  rs.close
  set rs = nothing
  set db = nothing

 

[JohnLee]

Good Day tehNellie,

Thanks for your response, I'll let you know how I get on. Just some info on the Ambersand in the table name, I've been using the ambersand for a number of years as part of the the table name and so far have not experienced any problems, but I've not had to use any code to date to extract anything from it as it has always been done using queries, so perhaps this may be the point in which I experience that.

Thanks

John

 

[tehNellie]

It wont cause you problems in terms of your database crashing, catching on fire and eloping to Mauritius with your wife but it is bad practice to use spaces, special characters and reserved words into table and column names.

& has a specific function in VBA and Access and sticking it in a table name makes your life more difficult later on down the line when you come try and resolve problems. Likewise sticking spaces in table/column names. It doesn't technically break anything but you have to do more typing, more checking, more double checking that you got the column name right when something doesn't work.

 

[JohnLee]

Hi tehNellie,

I said I would let you know how I got on, your assistance has been most helpful.

Below is the complete code that I've come up with, however I need a little more help as the code runs really slow, in that it does what I want it to do, but it is taking a long time to copy and rename the tif files, and there was only 51 of them. It took over 12 minutes which is a concern because when I go live with this there will be hundreds of files. If you wouldn't mind having a look at my code and let have your observations on how I could improve it, I would be most grateful.

[COLOR=blue]Function[/COLOR] StandardDGQuesImages()
[COLOR=blue]On Error Resume Next[/COLOR]
    
    [COLOR=blue]Dim[/COLOR] rst [COLOR=blue]As[/COLOR] DAO.Recordset        [COLOR=darkgreen]'Declare rst as DAO Recordset[/COLOR]
    [COLOR=blue]Dim[/COLOR] DB [COLOR=blue]As[/COLOR] Database              [COLOR=darkgreen]'Declare DB as the Database[/COLOR]
    [COLOR=blue]Dim[/COLOR] FS [COLOR=blue]As[/COLOR] FileSystemObject      [COLOR=darkgreen]'Declare the File System Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] Folder [COLOR=blue]As[/COLOR] Folder            [COLOR=darkgreen]'Declare the Folder Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] File [COLOR=blue]As[/COLOR] File                [COLOR=darkgreen]'Declare the File Object[/COLOR]
    [COLOR=blue]Dim[/COLOR] TifFilePath [COLOR=blue]As[/COLOR] String       [COLOR=darkgreen]'Declare the TifFilePath as a string[/COLOR] [COLOR=darkgreen]variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] NameOfFile                  [COLOR=darkgreen]'Declare the NameOfFile variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] strTemp [COLOR=blue]As[/COLOR] String           [COLOR=darkgreen]'Declare the strTemp as a string v[/COLOR]ariable
    [COLOR=blue]Dim[/COLOR] FileLoc [COLOR=blue]As[/COLOR] String           [COLOR=darkgreen]'Declare the FileLoc as a string var[/COLOR]iable
    [COLOR=blue]Dim[/COLOR] TopFolder [COLOR=blue]As[/COLOR] String         [COLOR=darkgreen]'Declare the TopFolder as a string variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] FormCode [COLOR=blue]As[/COLOR] String          [COLOR=darkgreen]'Declare the FormCode as a string variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] PlanNumber [COLOR=blue]As[/COLOR] String        [COLOR=darkgreen]'Declare the PlanNumber as a string variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] BatchID [COLOR=blue]As[/COLOR] String           [COLOR=darkgreen]'Declare the BatchID as a string variable[/COLOR]
    [COLOR=blue]Dim[/COLOR] ImagePath [COLOR=blue]As[/COLOR] String         [COLOR=darkgreen]'Declare the ImagePath a string variable[/COLOR]
    
    [COLOR=blue]Set[/COLOR] DB = CurrentDb                                  [COLOR=darkgreen]'Set DB as the current Database[/COLOR]
    [COLOR=blue]Set[/COLOR] FS = CreateObject("Scripting.FileSystemObject") [COLOR=darkgreen]'Set the File System Object[/COLOR]
    [COLOR=blue]Set[/COLOR] rst = DB.OpenRecordset("tblStdD&GNewBrandAdd")  [COLOR=darkgreen]'Set the rst to the tblStdD&GNewBrandAdd table[/COLOR]
    
    [COLOR=blue]Do While Not[/COLOR] rst.EOF [COLOR=darkgreen]'Loop until the end of the file[/COLOR]
        [COLOR=blue]If Not[/COLOR] rst.EOF [COLOR=blue]Then[/COLOR]
 
            FormCode = rst![strFormCode]        [COLOR=darkgreen]'Assign the field strFormCode to the variable FormCode[/COLOR]
            PlanNumber = rst![strPlanNumber]    [COLOR=darkgreen]'Assign the field strPlanNumber to the variable PlanNumber[/COLOR]
            BatchID = rst![strBatchID]          [COLOR=darkgreen]'Assign the field strBatchID to the variable BatchID[/COLOR]
            ImagePath = rst![strImagePath]      [COLOR=darkgreen]'Assign the field strImagePath to the variable ImagePath[/COLOR]
            
            TopFolder = Mid(ImagePath, 4, 37)   [COLOR=darkgreen]'Assign the Top Folder starting point i.e. 20120114\00040243_DOMESTICGENERAL_001[/COLOR]
        
            [COLOR=darkgreen]'Set the tif file path here [the location where tif files are stored]i.e J:\20120124\00040243_DOMESTICGENERAL_001[/COLOR]
            TifFilePath = "J:\" & TopFolder
                [COLOR=darkgreen]'If the text file folder can't be read then, stop running[/COLOR]
                [COLOR=blue]If Not[/COLOR] FS.FolderExists(TifFilePath) [COLOR=blue]Then[/COLOR]
                    MsgBox "Folder Doesn't Exist", , "Reading Tif Files"
                    [COLOR=blue]End[/COLOR] [COLOR=darkgreen]'End the process[/COLOR]
                [COLOR=blue]End If[/COLOR]
        
                [COLOR=darkgreen]'Get the path to the tif files in this case J:\20120124\00040243_DOMESTICGENERAL_001[/COLOR]
                [COLOR=blue]Set[/COLOR] Folder = FS.GetFolder(TifFilePath)
                    [COLOR=darkgreen]'Loops through Folders looking for Files[/COLOR]
                    [COLOR=blue]For Each[/COLOR] File [COLOR=blue]In[/COLOR] Folder.Files
                        [COLOR=darkgreen]'Get the tif file name[/COLOR]
                        NameOfFile = GetAttr("File.Name")
                        [COLOR=darkgreen]'If the file found has a ".tif" extension then[/COLOR]
                        [COLOR=blue]If[/COLOR] Right(File.Name, 4) = ".tif" [COLOR=blue]Then[/COLOR]
                            [COLOR=darkgreen]'Assign the text file name to the strTemp variable i.e. 00040243_DOMESTICGENERAL_001_10.tif[/COLOR]
                            strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1)
                            [COLOR=darkgreen]'Assign the Folder and File.Name to the FileLoc variable i.e. J:\20120124\00040243_DOMESTICGENERAL_001\00040243_DOMESTICGENERAL_001_10.tif[/COLOR]
                            FileLoc = Folder & "\" & File.Name
 
                                [COLOR=blue]If[/COLOR] FileLoc = ImagePath [COLOR=blue]Then[/COLOR]
                                [COLOR=darkgreen]'Copy tif file to new location and rename[/COLOR]
[COLOR=darkgreen]                               FS.CopyFile Folder & "\" & strTemp, "H:\John Lee\StdDGImages" & "\" & FormCode & "\" & PlanNumber & ".tif"[/COLOR]
                                
                                [COLOR=blue]End If[/COLOR]
[COLOR=blue]                       End If[/COLOR]
                    DoEvents
                    [COLOR=darkgreen]'Indicates in the progress bar the file being worked on[/COLOR]
                    DoCmd.Echo True, "Copying Std Ques Files to New Location: " & Folder & "\" & strTemp & "To: " & "H:\John Lee\StdDGImages" & "\" & FormCode & "\" & PlanNumber & ".tif"
                [COLOR=blue]Next[/COLOR]
        [COLOR=blue]End If[/COLOR]
        [COLOR=darkgreen]'Move to the next record[/COLOR]
        rst.MoveNext
    [COLOR=blue]Loop[/COLOR]
    
    rst.Close
    [COLOR=blue]Set[/COLOR] rst = [COLOR=blue]Nothing[/COLOR]
    [COLOR=blue]Set[/COLOR] DB = [COLOR=blue]Nothing[/COLOR]
 
[COLOR=blue]End Function[/COLOR]

John

 

[Kiwiman]

...and eloping to Mauritius with your wife...

Pity about that

 

[JohnLee]

What you talking about, Pity about what, I haven't a clue what that message is all about.

Please explain

John

 

[Kiwiman]

Howzit

it was in reference to part of the quote... sometimes called humour

 

[JohnLee]

Well Kiwiman,

I'm all for a bit of humour, but it usually helps the audience if they know it's homour. "Howzit" didn't do it for me as I had not seen the quote you highlighted in any of my posts.

So I take it you have nothing to assist with my post then

John

 

[Kiwiman]

Howzit

Who stole the jam out of your doughnut???

That's the thing about humour - everyone has different tastes It was thenellie who stated it not you. No need to contribute to your problem as thenellie has it all in hand.

 

[tehNellie]

I've not run any testing on it, but at first glance there doesn't appear to be much obviously wrong with the code.

Some points that spring immediately to mind

1) You're checking to see if the file is a .tif which suggests there might be other files in that directory? Your code checks every file in the directory, if you have lots of non .tif files also stored there that will slow down the code as it loops and checks each file. If there are lots of non tif files there and you can't do anything about it, perhaps output a "Dir *.tif" into a file and loop through that to ignore any non .tif files? (there may be a more efficient way to do that, it's been a while since I messed with the FSO)

2) You appear to be copying between two network drives which is another obvious bottle neck. Our network here appears to be designed using spaghetti which frequently dries out and slows everything down. If you were to replicate that code to copy between two local directories on your workstation how does the performance compare?

3) What size approximately are the files? That will obviously influence the time it takes to copy them from one location to another especially in combination with 2.

4) You have 'on error resume next' set which is always a bad idea when testing code (and generally imo), you're essentially turning off the error handling so anything that is going wrong is not being reported back to you.

5) When you're trying to identify a bottle neck, I find populating a variable with your start time before you do an action and a finish time and outputting a datediff between the two comes in handy. An obvious place to start is

 StartTime = Now()
 
   'Copy the file
 
 EndTime = Now()

 debug.print "Time taken to copyfile =: " &  datediff("s", StartTime, EndTime)  & " Seconds"

hope that helps.

 

[JohnLee]

Hi tehNellie,

Thanks for your observations I will let you know how I get on with those.

Cheers.

John

 

[JohnLee]

Hi tehNellie,

Taking on board you observations I've removed the part that checks to see if the file is a tif file because all the files in that particular location are tif files and will only ever be tif files. so that has increase the speed of the code significantly, thanks

The files will be copied across the network from one directory to another so we will have to live with the time it takes for the code to do it's work.

The tif files do fluctuate in size and so again that's something we will have to accept will impact on the time it takes the code to do its work.

I've tested the code again taking on board all you observations and we are happy with the results we are getting.

Thanks very much for your assistance.

John