Access 2010 VBA Parameter Query

[Kurt]

Hello All,

I have the following code that I inherited:

'Import the updated list
    
    'Variable Declaration
    SrcFilePath = ThisWorkbook.Path & "\01_Database.accdb"
        SrcQuery = "SELECT [QryFNL_Report].* FROM [QryFNL_Report];"
    TgtSheet = "DATA_Clean"
    Tgt1stCell = "iPt_NewData"
    TgtHeaderOffset = 8
    ' Create Parameter Object.
    Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 5)
    Param1.Value = " "
    Cmd1.Parameters.Append Param1
    Set Param1 = Nothing

I know this code is wrong how do I just press the enter key in the code to be able to use the parameter query report from the Access DB?

 

[Micron]

Who says the problem is a user name? What about the "|" you have in your posted path? I've never seen a path like that.
And who is Smitty? Have you posted this problem somewhere else?

 

[Kurt]

Who says the problem is a user name? What about the "|" you have in your posted path? I've never seen a path like that.
And who is Smitty? Have you posted this problem somewhere else?

It is the User Name path. There is no | in the posted path I only see the \. I tried to change these parameters to get it to work on my laptop at home now I am at work and the username and path are ok.

Again this program worked fine until somebody moved it either from another machine or from a server. I inherited this application.

If I am wrong quote it here.

Smitty is Chris Smith who is one of the moderators of the Excel Forum whom I have know since 2002 and he is a Excel MVP.

We use a Excel front end to connect to the database in Access and then bring the other data into Excel.

 

[james_lankford]

C:\Users\11111111\Desktop|Disbursements\Disbursements-DebtBalance

it's right there between Desktop and Disbursements

 

[Kurt]

C:\Users\11111111\Desktop|Disbursements\Disbursements-DebtBalance

it's right there between Desktop and Disbursements

He said it was a | pipe symbol it is a hyphen and it should be an underscore Disbursements_DebtBalance.

Come on guys who cares about this symantic type question?

I suspected I knew where the problem was and Smitty figured it out with me online.

Someone had overwritten a header file in a Access Table called CLS_Current and a date was somehow placed there.

I corrected it and now the code is running perfectly.

So what I have learned is to check for srcQuer and FilePath in the Immediate Window in Excel VBA and I have learned to verify the path name at a break point.

I have also learned more and more to look in the not so obvious places like this.

Thanks all we can mark this thread solved.

 

[james_lankford]

it IS a pipe symbol between Desktop|Disbursements

and if the error message you were getting was "invalid path" then the difference \ and | are VERY important