Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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
0
robsking
Asked:
robsking
  • 8
  • 8
  • 5
  • +1
2 Solutions
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now