Funtion to download file while referencing path located in database field.

The path to the users folder is located in the masterdatabase. The error is on the word named path. Any solutions? Thanks



Public Function basImportDatabase()

Dim dbCheck As DAO.Database
Dim rst As DAO.Recordset

Set dbCheck = DBEngine.OpenDatabase("X:\masterdatabase.mdb")
Set rst = dbCheck.OpenRecordset("SELECT tblMasterUser.Path FROM tblMasterUser WHERE ((tblMasterUser.District_Manager) = 'Joe Smith')")


On Error Resume Next
 DownloadFile "X:\Database.mde", " & rst("Path") & "Database.mde"
Set rst = Nothing
Set dbCheck = Nothing

End Function
robskingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RDWaibelCommented:
the .path could be an issue.
am I assuming correctly then this is the line that is failing?
{Set rst = dbCheck.OpenRecordset("SELECT tblMasterUser.Path FROM tblMasterUser WHERE ((tblMasterUser.District_Manager) = 'Joe Smith')")}

you may need to change the field name to UserPath
0
robskingAuthor Commented:
Is Path a reserved word?
0
RDWaibelCommented:
yes, in VB.  as VBA is a 'type' or subset of VB.  this could be the whole issue.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

robskingAuthor Commented:
Just changed it to userpath. It's not the problem.
0
RDWaibelCommented:
what line is failing, the select statement?
0
robskingAuthor Commented:
This line is in red.

DownloadFile "X:\Database.mde", " & rst("UserPath") & "Database.mde"
0
stevbeCommented:
have you debugged the return from rst("Path") ?

using On Erro Resume next will make this difficult ... can you tell us what the specific error is?

MsgBox rst.Fields("Path").Value

Steve
0
RDWaibelCommented:
in the immediate window, type

?rst("UserPath")

press enter and tell me what is says.
0
robskingAuthor Commented:
compile error sub or function not defined
0
knollbertCommented:
what form does Path take??

if path = C:\hi
  it will attempt to download c:\hidatabase.mde

0
stevbeCommented:
... is the path missing the final back slash ?

DownloadFile "X:\Database.mde", " & rst.Fields("UserPath").Value & "\Database.mde"

Steve
0
knollbertCommented:
try rst!path
0
robskingAuthor Commented:
Yes. Path = c:\
or c:\windows\desktop
0
RDWaibelCommented:
what does "DownloadFile" do?
0
stevbeCommented:
Public Function basImportDatabase()

Dim dbCheck As DAO.Database
Dim rst As DAO.Recordset
Dim strPath As String

Set dbCheck = DBEngine.OpenDatabase("X:\masterdatabase.mdb")
Set rst = dbCheck.OpenRecordset("SELECT tblMasterUser.Path FROM tblMasterUser WHERE ((tblMasterUser.District_Manager) = 'Joe Smith')")

strPath = rst.Fields("UserPath").Value & vbNUllString

'see if anything was returned
If Len(strPath) = 0 Then
    MsgBox "No Path returned from database"
    Exit Functrion
End If

make sure the trailing back slash exists
If Right(strPath,1) <> "\" Then
    strPath = strPath & "\"
End If

DownloadFile "X:\Database.mde", " & rst("Path") & "Database.mde"

'cleanup
rst.Close
Set rst = Nothing
dbCheck.Close
Set dbCheck = Nothing

End Function

Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RDWaibelCommented:
the error "compile error sub or function not defined" means it can't fund a function or sub that you have called.
0
stevbeCommented:
my code will change

c:\windows\desktop to c:\windows\desktop\ which is the issue ... no trailing backslash

so next question is ... will DownloadFile create the "Path" directory if it does not exist or do you need code to do that before the download?

Steve
0
robskingAuthor Commented:
Steve,
This line is red.
DownloadFile "X:\Database.mde", " & rst("Path") & "Database.mde"
0
stevbeCommented:
what is the signature for DownloadFile ... how many parameters is it expecting? does it build the destination folder if it does not exist? I ask these questions because DownloadFile is not a builtin Access function.

Steve
0
robskingAuthor Commented:
Thanks guys!
0
RDWaibelCommented:
I take it you fixed it?
0
robskingAuthor Commented:
Yes. I played with the Windows API a bit. Thanks
0
RDWaibelCommented:
very good!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.