使用DoCmd.TransferText网络权限将csv文件导入ms access

Question

我有一个简单的vba脚本，用于将csv从网络共享导入到access数据库的新表中。我可以访问网络共享，它在我的机器上运行得很好。我希望其他没有访问网络共享的人也能够执行脚本。我有一个通用的网络帐户与所需的权限，我希望我可以嵌入到脚本中？

我已经看过这个论坛和其他论坛，MSDN找不到如何做到这一点，大多数似乎与数据库和数据库连接权限有关。

谢谢你

Sub Import_data()

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Soil_moisture_data"
DoCmd.SetWarnings True
DoCmd.TransferText acImportDelim, , "Soil_moisture_data", "full network path and file", True
MsgBox "Soil moisture data has been updated"

End Sub

Answer 1

这是一个解决方案。它创建网络位置的本地映射网络驱动器，然后导入数据，就像从本地驱动器中的csv文件导入数据一样；完成后，它将删除映射驱动器。

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set wshNet = CreateObject("WScript.Network")

strUsername = Domain\username

strPassword = Password

strDestination = GetNextLetter("F", wshNet)

wshNet.MapNetworkDrive strDestination, "\\ServerAddress\Folder", , strUsername, strPassword

DoCmd.TransferText acImportDelim, , "Daily_data", strDestination & "\RestOfThePathIncludingFileName", True

wshNet.RemoveNetworkDrive strDestination, True

Set objFSO = Nothing
Set wshNetwork = Nothing

此解决方案是在https://www.experts-exchange.com/questions/21108808/using-vba-to-copy-file-to-a-network-share-with-username-and-password.html之后完成的

Function GetNextLetter(DriveLetter, wshNet)

   'Start x at the ascii value of the drive letter to start the search
   'unless something is passed in. This sample uses capital letters and
   'starts at F.

   If IsEmpty(DriveLetter) Then

    x = 70

   Else

    x = Asc(DriveLetter)

   End If   

   Set oDrives = wshNet.EnumNetworkDrives
   'Step by two since the first item in the collection is the drive letter
   'and the second item is the network mapping

   For i = 0 To oDrives.Count - 1 Step 2

    If Chr(x) & ":" = oDrives.Item(i) Then

        x = x + 1

    End If

   Next   

   GetNextLetter = Chr(x) & ":"

End Function