Calling variables from a Table

[daievans]

I am trying to modify some code that we use to move data files around our system, and we currently use Access for this purpose. It may not be the optimal way of doing it, but there's a great attachment to the software here, and I can't see it going away!

However, in order to make the code a little more robust and manageable, I'd like to hold the variables in a table which I can refer to as we loop through the 20 or so combinations of file locations, file format masks, file extensions etc ...

Anyone done anything similar and have any inspiration for me? I'm not an Access wizard so go easy on my lack of ideas :banghead:

Code examples will be greatly appreciated, as will any potential prattfalls you've encountered using such an approach.

Many thanks in anticiipation.

dai

 

[Bilbo_Baggins_Esq]

a little confused here.

Do you mean you want to house the actual variable (names) in a table, or the values you would want to apply to variables whose names already exist in your code?

 

[daievans]

a little confused here.

Do you mean you want to house the actual variable (names) in a table, or the values you would want to apply to variables whose names already exist in your code?

Thanks BBE - it's the latter, values I want to apply to variables already defined.

I want the code to execute based on the values of each record in a table. Typically the values are mnemonics that we use to create file names, file locations etc. They will reside in a table (myfilelocations).

I am having difficulty with relating the variables to the value in the table - can't get my head around that particular concept - I'm close, but not quite there

 

[NigelShaw]

I am trying to modify some code that we use to move data files around our system, and we currently use Access for this purpose. It may not be the optimal way of doing it, but there's a great attachment to the software here, and I can't see it going away!

However, in order to make the code a little more robust and manageable, I'd like to hold the variables in a table which I can refer to as we loop through the 20 or so combinations of file locations, file format masks, file extensions etc ...

Anyone done anything similar and have any inspiration for me? I'm not an Access wizard so go easy on my lack of ideas :banghead:

Code examples will be greatly appreciated, as will any potential prattfalls you've encountered using such an approach.

Many thanks in anticiipation.

dai

Hi

I totally get what you're saying. I cant see the 'real' benefit however. To store variables in a table. you would still need to declare them to use them. To me, that kinda reads that more code would be required......

If you can provide a smaple of what you are trying to achieve, i will do my best to create what you want.

I code in a similar way to this but keeping my variables in a global list. Here is an example of what i would typically do (bear in mind, this is adhoc lol)

'GLOBAL MODULE
Option Explicit

'set up my specific variables
Public sString1 As String
Public sString2 As String
Public sString3 As String
Public sString4 As String
Public sString5 As String
Public sString6 As String

sString1 = "TAXI"
sString2 = "TRAIN"
sString1 = "PLANE"
sString1 = "BUS"
sString1 = "BIKE"
sString1 = "WALK"

'COLLECTION MODULE
Option Explicit

Sub AnyModule()
'any module can call SetCollection and use a preset variable
call SetCollection(sString3)
End Sub

Sub SetCollection(stGetString As String)
Forms!MyForm!MyTextField.Value = stGetString
End Sub

theoretically i could now change my variables only, leaving my code intact without searching through pages to make changes. the code above would change the Field "MyTextField" to whatever stored value you wanted. In your case, you could store in a table and call with a DAO recordset. Lets say your table is called tblVars-

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim i As Integer

Set DB = CurrentDb()
Set RS = Db.Openrecordset("tblVars", dbOpenDynaset )

'move to the first record
With RS
   If Not .EOF or .BOF Then
   .MoveFirst

   'count records
      For i = 1 to .Recordcount
      sString1 = !strVar
      Next i
      Loop
   End If
End With

Set RS = Nothing
Set DB = Nothing

RS.Close
DB.Close

something similar to this would collect the variable values from the table tblVars and set the public variables with the values. You could then change the values in the table from a form using a DAO recordset edit method.

Hopefully, the code works. As i said, its adhoc from my phone so typos etc etc

If you want to let me have a look at your db, PM me for my email. Im on the right lines i think?



Thanks



Nigel

 

[gemma-the-husky]

actually, access is a great tool for general programming

i tried to develop a file backup system in access. I developed a nice interface, but the project failed to some extent, because access longints only go to 2billion (or 2 gigabyte) - and comparing file sizes of larger files to see if they needed backing up failed, and I could not find a way round it.

 

[daievans]

Nigel -

You've nailed it, Sir! That's exactly what I was trying to do.

My thinking with the table was that there are a whole lot of permutations of files, locations and mnemonics to manage and by my simplistic way of operating, the table was a "neat" solution - however, I am not a programmer per se - I'm a dilettante

I figure that adding, amending the data would be easier if it was in a table .... and I want the current analyst on the project to have an easier life - currently he spends a lot of time keeping the process going with a piec of code for each instance ..... ahhh! But it does keep him busy (doing nothing, working the whole day through ....)

Am looking forward to having a crack at it this weekend! No beach or golf for me this weekend!

Thanks again Nigel

dai

 

[NigelShaw]

Hey

glad to be of help. Give me a shout if you need any help implementing this


Thanks



Nigel

 

[daievans]

actually, access is a great tool for general programming

i tried to develop a file backup system in access. I developed a nice interface, but the project failed to some extent, because access longints only go to 2billion (or 2 gigabyte) - and comparing file sizes of larger files to see if they needed backing up failed, and I could not find a way round it.

That issue has reared its head here also - but they do insist on writing all manner of unecessary data, and using single tables.

 

[Solo712]

actually, access is a great tool for general programming

i tried to develop a file backup system in access. I developed a nice interface, but the project failed to some extent, because access longints only go to 2billion (or 2 gigabyte) - and comparing file sizes of larger files to see if they needed backing up failed, and I could not find a way round it.

Not that much of a problem, I don't think .

Public Function FileSize(fname As String) As Double
Dim fso As Object, f As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(fname)
FileSize = f.Size
Set f = Nothing
Set fso = Nothing
End Function

Best,
Jiri

 

[gemma-the-husky]

Not that much of a problem, I don't think .

Public Function FileSize(fname As String) As Double
Dim fso As Object, f As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(fname)
FileSize = f.Size
Set f = Nothing
Set fso = Nothing
End Function

Best,
Jiri

thanks. i assumed i would not get more accuracy with a double, but i will have a go.

 

[Solo712]

thanks. i assumed i would not get more accuracy with a double, but i will have a go.

Np, Dave. IIUC, the fso route is likely the only one that works for bigger files. Trying to use the high Dword in GetFileSize(ex) apparently does not produce correct readings. I did not even try after I read it.

Best,
Jiri

 

[daievans]

The Proof of Concept performed nicely - thanks to all for assistance/support/ideas ....

Now to implement the complete solution .... :banghead:

 

[daievans]

Hello Hello Hello ...

So there I was Looping nicely through a table of variables until I go to the point where I wanted to call another subroutine and "use" the variables from that recordset in the table. The subroutine seems to want me to set the variables again - is that becasue the code has "re-"dim"med them? (I'm trying to be economical and reuse some old code I found lying about here - I know, its probably an error ...) I'll post the code shortly - it will make it a lot easier for you kind folks to undertsand my (access) problem ...

Hope everyone's having a laugh today - Cardiff City in the Premiership! Let the Swans v Bluebirds rivalry continue ... just get some old roling stock to transport the fans back and forth ..

 

[Bilbo_Baggins_Esq]

Are the variables being used within the same module?

If so, set Dim the variable at the top, outside the actual Function, Private Sub, or Sub in which it is set.
That way it is usable by all the code within that module.

However, if they are being used across different modules, you need to make the variables Public.

I usually set my public variables in a completely seperate module such as:

Public StringVar As String
Public DoubleVar as Double
etc.

 

[NigelShaw]

Hey

Post your code. I wouldn't necessarily make the variable public unless its required elsewhere. Remember that it will hold data unless its emptied. I would change the called routine to something like

sub MyOutsideRoutine(sString As String)

Or whatever the variable needs to be. Then, from within your loop, you can call it

call MyOutsideRoutine("whatever")

Where "whatever" is the condition of the outside call. Your code would really help and clear things up in a jiffy



Cheers

Nidge

 

[daievans]

Thanks gents - will work on it in the am here, when you'll be home having afternoon tea and choccy biccies ...

As usual, the brains around here cracked it ....