主页 / user-88192

MGaddict's questions

Martin Hope
MGaddict
Asked: 2017-04-22 09:14:00 +0800 CST
  • 0

我写了一些 VBA 来轻松地将 excel 电子表格导入到我的数据库中的表中。这样，用户只需单击表单上的一个按钮，并回答有关要导入的文件的一些问题。问题是导入完成后，我尝试修改数据库中的任何其他内容，但收到消息“此时您没有对数据库的独占访问权限。如果继续进行更改，您可能以后不能救他们了。” 然后我必须退出数据库并重新打开它以进行任何更改。我以前在其他版本的 Access 上做过这个没有问题。所以我不知道是我在某个地方有错字，还是从 Access 2013 到 2016 发生了某些变化导致了这个问题。目前，数据库位于我的本地机器上。最终，这将被移动到 SharePoint 站点并拆分为 2 个访问文件。在它主要工作之前我不想这样做，因为我没有任何地方可以上传到 SharePoint 而没有很多人可以访问它。

Private Sub ImportNewTB_Click()
On Error GoTo ImportNewTB_Click_Err 'If an error occurs anywhere along the way, make sure you still clean up the memory before quiting

Dim OwssvrFile As DAO.Database 'This is the open connection to the file
Dim OwssvrInfo As DAO.Recordset 'This is the recordset for the teachers information
Dim fileName As String 'This is the name of the file being opened
Dim dbs As DAO.Database
Dim CurrentTBDB As DAO.Recordset 'This is to make a connection to our current table
'Dim ExcelHdrs(0 To 20) As Variant 'This is an Array with the headers from the Excel file.
Dim numRecords As Integer
Dim WksName As String
Dim TimeStamp As Date

fileName = getOpenFile() 'Use the function I built in the Module
If fileName = "" Then GoTo ImportNewTB_Click_Exit 'If they didn't select anything, then just give up on life and exit

WksName = InputBox("Enter the name of the worksheet: ", "Worksheet Name", "owssvr")
numRecords = InputBox("Enter the number of records in the Worksheet: ", "Num Records", 2412)
WksName = WksName & "$A1:BE" & numRecords + 1

'Once we have a real file, open it already
Set OwssvrFile = OpenDatabase(fileName, False, True, "Excel 12.0; HDR=YES;")
'Create Recordset from the excel file.
Set OwssvrInfo = OwssvrFile.OpenRecordset(WksName)
OwssvrInfo.MoveFirst 'Goto the first line of the recordset

Set dbs = CurrentDb
Set CurrentTBDB = dbs.OpenRecordset("SELECT * FROM CurrentTB")

TimeStamp = Now()

Do
    With CurrentTBDB
        .AddNew
        .Fields!EntryDate = TimeStamp
        .Fields!ProjectName = OwssvrInfo.Fields(0)

        .Update
    End With

    OwssvrInfo.MoveNext 'All of that for entry 1, only 2000 more lines to go
Loop Until OwssvrInfo.EOF

' Tidy up, This closes everything out and releases the memory
ImportNewTB_Click_Exit:
On Error Resume Next 'Basically this says, if there's an error, I don't care, do this anyway
MsgBox "Input Complete!"
OwssvrInfo.Close
OwssvrFile.Close
CurrentTBDB.Close
Set CurrentTBDB = Nothing
Set dbs = Nothing
Set OwssvrInfo = Nothing
Set OwssvrFile = Nothing
Exit Sub

ImportNewTB_Click_Err: 'This produces an error message if one exists
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error!"
Resume ImportNewTB_Click_Exit 'Make sure we still clean up before leaving
End Sub
vba ms-access-2016
Martin Hope
MGaddict
Asked: 2016-02-26 13:16:02 +0800 CST
  • 0

我只是觉得我错过了一些非常愚蠢的东西。我有一个简单的访问数据库，用于跟踪我的项目。我有多个人，我有多个项目。对于该项目，我有一个下拉列表，我可以从我的员工列表中选择该项目的负责人。没问题。但是，它只显示此人的名字。我如何让它显示名字和姓氏？

  • 表 1：ID、名字、姓氏
  • 表 2：ID、lead、notes——“lead”是一个长整数。

行源是

SELECT [Table1].[ID], [Table1].[lastName], [Table1].[firstName], [Table1].[Position]
FROM Applicants
ORDER BY [lastName], [firstName];

在表格上，我得到了下拉框。它显示名字、姓氏和他们的职位，但一旦我选择了我想要的人，它只显示名字。

数据库比这复杂得多，但其他信息都不相关。

ms-access