Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: getting field data type information

    Take a glance at the code below:

    Code:
        Set db = CurrentDb
        Set rs = db.OpenRecordset("match_ipdkolonnenavne", dbReadOnly)
        
        ipdkolonnenavn = Trim(rs!franavn.Value)
        
        Set rs = db.OpenRecordset("import", dbReadOnly)
        
        ipdkolonnetype = Import.ipdkolonnenavne.fieldtype
    I first set ipdkolonnenavn to a value from a query. This query returns the column name from a specic table.

    ipdkolonnetype should be set to the field type of the ipdkolonnenavn specific column in a different table. Return whether its int or string. How do I create the expression currently written "ipdkolonnetype = Import.ipdkolonnenavne.fieldtype" as its an expression to be run and not a string.

    Thanks in advance,

    Kenny
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose you could check to see if the value is numeric, or use other such test to see if you can otherwise qualify the varaible prior to use

    have a look at isnumeric, isnull, isempty, isdate functions etc

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    isnumeric doesn't qualify for the future operations in the procedure, it would practically double the amount of code for me rather than determining the datatype from the beginning and lock or change it.

    there has to be a way to do determine the type by code.. and the expression is pretty simple too, but turning a string into an expression doesn't seem that easy, and EVAL() doesn't do it.

    Code:
        Set db = CurrentDb
        Set rs = db.OpenRecordset("match_ipdkolonnenavne", dbReadOnly)
        
        ipdkolonnenavn = Trim(rs!franavn.Value)
        
        Set rs = db.OpenRecordset("import", dbReadOnly)
        
        ipdkolonnetype = "Import." & ipdkolonnenavn & ".fieldtype"
        
        ipdkolonnetype = Eval(ipdkolonnetype)
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm pretty dertain that by using the ADOX recordset collection you can find what dataype a particaulr column is.

    From what I remeber the settign is an integer, or possibly a byte and it is not typed, but wiuth a bit of investigation you can derive the datatype from a column defintion.

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    I've been diggin into the ADOX datatypeenum function, but I can only get this value by using an expression, which is the same basic problem as I already had.. the expression is being built as a string first in the procedure, as the column that I want to check varies depending on user input.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I forget the detail offhand, but I think its in the tabldefs collection, you cna iterate through various collections to read what the tabel, and column defintion is. You can then assign things as required. However, and its a big howevrr I think it will be a monstrous task to create a generic routine to do this. It would be simpler, but by no means a piece of cake, to do this if VBA had true object ineritance.

    Just out of curiosity waht is driving you down this route?

    the reason I suggested the isnumeric route is that that should filter off any numeric data to be handled one way.

    I'm not sure you can do the assignment as easily as you wish, you will probably to do a great deal of VBA coding to get anywhere near the functionaility you seem to want. Given the problems of VBA in terms of protability, ease of maintenance etc I doubt its appropriateness in this instance. it may be better to consider writing a dll in somehting like vb or c++.

  7. #7
    Join Date
    Oct 2005
    Posts
    183
    What is driving me is a set of queries that depend upon dynamic imports of excel spreadsheets. I have no way of knowing if Access decides to import a column as string or a value, as the import is surpressed to automation and I don't know the column names beforehand.

    During some of the procedures operating on the imported data are simple >=< operators, and they only work with numbers ( > 8) whereas I would have to choose > "8" if the column would be a string.

    For the moment I have worked around the problem by doing an "is not null" as that is the most important thing during development. But before the front-end goes out I'll have to surpress any error a > 8 might return and then run a > "8" instrad.... that is, if I can't create a procedure to run down the datatype of the imported columns and store them in a table / array for use throughout the data manipulation proces.

    Basically what the front-end does is import a user chosen excel spreadsheet, lets the user choose a series of match fields, runs a series of matches against our datawarehouse, returns the results, joins them with all the data from the spreadsheets and returns a new spreadsheet.

    Most of the matching and data manipulation is done on a SQL-server (and thank God for that) but some of them has to be done in access. Regardless of this, the isnumeric function would do well if we were talking 500 records, as just one test failing would dictate a string rather than a value. But as we can be talking 100.000 records +, it just wont last in the long run.

    Hope the above gives you a tiny insigt into the project I'm on... and yarh, well, access does pretty well actually. I do some VB programming outside Access too, but I'm not experienced enough with VB.net to do the same stuff yet, and the users want the front-end in access anyway.. probably because they think they can lure out and change the code or something.... *shrug*

    /Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •