Relative File Path

[achalrikhi]

Hello friends,

I am working on a project. I have tried few attempts but could not resolve the issue as I am not very good at vba as I am just a beginner. I am trying a DoCmd statement. Now the prblem is that I am working on three machines, office, home and laptop. all of them are having different file path for my project and the file I am trying to import through docmd statement.

My question is " Can we have a relative OR global file path for the file I want to import, so that the docmd statement will work on every computer. The statement I am using is this :

DoCmd.TransferText acImportDelim, "fo Import Specification", "TblFutures", FileName:="C:\Users\welcome\Desktop\Cash.csv", HasFieldNames:=True

Is it possible to write a file path which is global or relative or at least good enough to work on every machine ?

Please advise.

Regards

Achal

 

[Trevor G]

Is the database loaded in the same path as the CSV file?

 

[achalrikhi]

Is the database loaded in the same path as the CSV file?

Yes. It is a very small project so I have kept it on the desktop it self for the sake of convenience. And the file which is to be imported is also on the desktop.

Regards

Achal

 

[Trevor G]

You can use this then. Perhaps check out the path on the other computers first to make sure it is correct.

FileName:=CurrentProject.Path & "\Cash.csv", HasFieldNames:=True

 

[achalrikhi]

You can use this then. Perhaps check out the path on the other computers first to make sure it is correct.

Dear Trevor G,

This works fine but there are two things,

User will not be able to save the name as per their wish and secondly the file will be saved where the project is lying.

I just found out a way to this : FileDialog box for saving. This will not only enable the user to give a name for the file but the user will also be able to choose the path where he/she wants it to save. The code and the issue is :

Private Sub Command28_Click()


Dim intChoice As Integer
Dim strPath As String


'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made


If intChoice <> 0 Then
'get the file path selected by the user
strPath = _
Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
'displays the result in a message box
Call MsgBox(strPath, vbInformation, "Save Path")


DoCmd.TransferText acExportDelim, "fo Export Specification", "Fo", strPath, True
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM TblFutures;"
DoCmd.SetWarnings True
End If


End Sub

Now the issue is, when I run this code it gives me the error :

Run-time error '3027' :

Can not update. Database or object is read only.

Now please suggest a solution to deal with this.

Regards

Achal