Access to Sql (1 Viewer)

[GaryPanic]

Quickie questions

I am currently building a Dbase to replace a real nightmare of a system
doing it Access 2000 (Its what I'm happy with) any way, how easy is it to upgrade to front end access b/end sql (it would not be a job i would do) but from the discussions in the forum this looks like a relative easy job

thoughtsd

gbp

 

[pdx_man]

Yes, it is a very easy job to do. Migrate your queries to SP and create Pass-Through queries to them in Access. Parameters can be added to the SQL property of PT query by using a function like:

Public Function Update_PassThrough_Parameters(varQueryName As String, varParams As String) As Integer
Dim QryDef As dao.QueryDef
Dim varQuerySQL As String
Dim varCommaPos As Integer
Dim varNextParam As String

On Error GoTo SomethingDidNotUpdate

    Set QryDef = CurrentDb.QueryDefs(varQueryName)
    varQuerySQL = Left(QryDef.SQL, InStr(QryDef.SQL, "'") - 1)
    
    Do
        varCommaPos = InStr(varParams, ",")
        varNextParam = GetNextCSV(varParams, varCommaPos)
        varParams = Mid(varParams, InStr(varParams, ",") + 1)
        
        If IsNumeric(varNextParam) Then
            varQuerySQL = varQuerySQL & varNextParam & ", "
        Else
            varQuerySQL = varQuerySQL & AddSingleQuotes(varNextParam) & ", "
        End If
    Loop Until varCommaPos = 0

    varQuerySQL = Left(varQuerySQL, Len(varQuerySQL) - 2)
    QryDef.SQL = varQuerySQL
    
    Update_PassThrough_Parameters = 1
    
    Exit Function
    
SomethingDidNotUpdate:
        Update_PassThrough_Parameters = Err.Number
        
End Function

Public Function AddSingleQuotes(varItem As String) As String

    AddSingleQuotes = "'" & varItem & "'"
    
End Function

Public Function GetNextCSV(varItem As String, ByRef ThePosition As Integer) As String
Dim varHoldStr As String
    
    If ThePosition = 0 Then
        GetNextCSV = varItem
    Else
        GetNextCSV = Trim(Left(varItem, InStr(varItem, ",") - 1))
    End If
    
End Function

 

[GaryPanic]

many thanks for this -
not a job I feel I could do at present, but as long as its do-able , then I can sit back and be happy - should I go down this route I'll get someone who knows what they are talking about , rather than wing it -

thanks for your reponse
gbp