Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80

    API Key?

    A client has a database that for several years now has successfully pulled data from the St Louis Federal Reserve system, and is now throwing an error. Looking at the VBA module, it occurs in the section where a link is created using an API Key, long string of letters and numbers. This is an area I know very little about, but my suspicion is there has been a change in that API key at the Fed end. Can anybody offer any insight? (The error, by the way, says that a user-defined type is not available, and I'm assuming that definition is stored in the API when and if we can get to it. It's definitely not anywhere in my client's Access database!)

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you mean "User-defined type not defined"?
    2 things come to mind in that case.

    The error usually means that something is being called/invoked/referred to that Access thinks is a type or function that it can't find, or a valid reference to a built in function or type is being made but the supporting library (reference) is not found.

    Less likely is that the code isn't ptrsafe - because that message is along the lines of "Compile error: The code in this project must be updated for use on 64-bit systems. ..."

    In either case, the major question is, has the customer updated anything; Office (32 to 64 bit), Access version, etc.
    I doubt the issue has anything to do with the data provider.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    That's helpful. Yes, the client has upgraded to Windows 10 and to Office ?2013? in the recent past. I did suggest they check for missing references, but I didn't see any via my remote logon. If the project (database) itself needs to be updated somehow, what's involved in that?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You didn't address the bitness of the Office versions.
    API calls are (most often) a means of working with aspects of the operating system, such as its functions, data services, objects, interfaces, etc. The reason I don't suspect the data provider to be the issue is that API's are native to Windows thus aren't controlled by some other entity. I'm not well versed in API functionality so even if you posted the relevant code I probably wouldn't spot the error if it's there and it's an API error.

    If things worked for some time but don't now, focus on what has changed. It could be as simple as a project reference (in vb editor - Tools>References) on the problems pc(s) is missing. Your description of the error location isn't all that helpful. I read it as 'it happens in this block of code where the API call is' not 'the API is responsible'.

    All I can suggest is post the relevant code and indicate on what line it fails and maybe one of us will be able to spot the issue, or at least get a better sense of the problem.

    Please use code tags (# on forum toolbar) for code.

  5. #5
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you checked with the Fed or logged into the Api Key account to see if its still valid?
    there appears to be 3 different Api's available https://research.stlouisfed.org/docs/api/

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to other suggestions, when did this API issue arise? Only after the Access version move? The move was done in parallel, right? So the older version is still available for testing?
    Agree with moke123 that confirmation of the account is key here. Ensure the account is active and that parameters are current to identify the "problem area" ---Access application/revised program version and logic vs the Fed has changed something.
    Good luck.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I thought the provider was only providing data. My bad.
    Sorry.

  8. #8
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    The client only uses this database once in awhile, last time in July I think, and the various upgrades have been going on for the past couple months, so I can't say any one of them caused the problem. But I did confirm that it now happens on two different 64-bit PCs that are both running Windows 10 Pro and MSOffice 2016. I too am thinking the API key may no longer be valid for some reason, and I've told the client to check with the St Louis Fed about that (I don't have the necessary access to do that). HOWEVER, the error occurs immediately at compile time, complains about a line that tries to Dim a new xxxxxx when said object definition apparently can't be found in any of the connected references. How that would relate to the validity or invalidity of the API key I don't really understand, since the error pops during compile, BEFORE a ny of the code has actually been run, including the call to the API key. I'll be very interested to see how this turns out.

  9. #9
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Assuming you developed in AC2010 and they are using AC2016 you may need to add PtrSafe. Did you check what the bitness is of the access version they have on those machines?

  10. #10
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    I checked with the Fed people and they said no changes at their end that would cause this, so I guess we're back to missing references. I added in a couple that looked promising to see if they fixed it, but no luck. I'm posting that section of code to see if anyone here has any ideas. What happens is that I get the "User-defined object not defined" error on compile, even before any code actually starts running; the error comes the first time I try to DIM an object in this FRED section of code. Presumably if I marked that line as a remark it would do the same thing at the next DIM statement. (FWIW, I got that error in a completely different part of the project regarding an Excel object, but adding in the relevant Excel library fixed that one.)

    Here's the code, the Dim statement that throws the first compile error is in a box of #######'s. Any and all thoughts welcome. Bear in mind this has been working fine for several years now, as recently as July.

    Code:
        'FRED Connection Information'
        Dim start_date As String
        
        API_Key = "6a1847ca64fdbc86bfe5385a80359a35"
        start_date = Format(mostRecentUpdate, "YYYY-MM-DD")
        API_Request_String = "https://api.stlouisfed.org/fred/series/observations?series_id=DTB3&observation_start=" & start_date & "&api_key=" & API_Key
        'Connect to FRED API'
        '########################################
        Dim Req As New XMLHTTP  '################
        '########################################
        Req.Open "GET", API_Request_String, False
        Req.send
        
        Dim Resp As New DOMDocument
        Resp.LoadXML Req.responseText
        
        Dim dailyData As IXMLDOMNode
        
        Dim stringSQL As String
        Dim rCnt As Integer
        
        'Update MMTC DB from FRED starting at recent date'
        For Each dailyData In Resp.getElementsByTagName("observation")
        
            tBillDate = dailyData.Attributes.getNamedItem("date").Text
            tBillValue = dailyData.Attributes.getNamedItem("value").Text
            
            If IsNumeric(tBillValue) <> True Then
                tBillValue = 999
                dailyTBillValue = 999
            Else
                'Calculate Daily T-Bill Value; Divide by 100 to convert to decimal'
                'Divide by 365 to convert to daily return'
                tBillValue = tBillValue / 100
                dailyTBillValue = tBillValue / 365
            End If
            
            tBillDate = Format(tBillDate, "MM/DD/YYYY")
            
            If tBillDate > mostRecentUpdate Then
                stringSQL = "INSERT INTO TBills([Date],[Value],[Daily T-Bill Value])"
                stringSQL = stringSQL & "VALUES (#" & tBillDate & "#," & tBillValue & "," & dailyTBillValue & ");"
                SysFunctions.insert (stringSQL)
            End If
        
        Next dailyData
        
        'Calculates values not available in FRED'
        Call Calc_Missing_Values(mostRecentUpdate)
    

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try adding "Microsoft XML, v3.0"

  12. #12
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Nope, it already has XML 3.0.

    I found something about not using Dim, instead creating an object and using that as my variable, will try that out today some time.

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Funny - when I added it, the error went away.

  14. #14
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Huh? Added it to what?

    FWIW, the alternative I found on one of the relevant websites was to drop the DIM statement and instead use this:

    'Dim Req As New XMLHTTP '################
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xmlHTTP.Open "GET", APIString, False

    I'm going to try that out on the client's PC and see what happens. I'm pretty sure that part in parentheses does not appear anywhere in the list of references, but hey, it's worth a try.

  15. #15
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    SOLVED, I think, though I honestly don't understand why. Their computer's references included MSXML 6.0, and when I "downgraded" it to 3.0 everything worked again. Thanks for all inputs here.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums