Turner2814
Turner2814

Reputation: 1

[Microsoft][ODBE Driver Manager] Data source name too long error in Excel

I have code that i took from a video on Youtube and adapted to my needs and i keep getting a Help with a "[Microsoft][ODBE Driver Manager] Data source name too long" error. I don't know why, I'm using the same code except for the file path. If someone could let me know what I'm doing wrong that would be awesome. I'm pretty much trying to make it so multiple people can update a workbook without being in it so i can avoid making it a Sharepoint. Here is a sample of the code:

Sub SyncFromDatabase()
LastLocalChange = Sheet1.Range("B8").Value
DbFile = Sheet1.Range("V7").Value 'Staff Database location

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)

If objFile.DateLastModified > LastLocalChange Then 'Database Change was 
made, Update Local Datebase
'Check Last Database Update
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provide=Microsoft.ACE.OLEDB.12.0;" & "Data 
Source=" & DbFile & ";Extended Properties=""Excel 12.0 
Xml;HDR=Yes;IMEX=0"";" '<---This is where i'm getting the error

objRecordset.Open "Select * From [StaffDb$]", objConnection 
',adOpenStatic ',adLockOptimistic ',adCmdText
Sheet2.Range("A2").CopyFromRecordset objRecordset
objRecordset.Close
objConnection.Close

RefreshStaffTable ' Update Contact Table
End If

End Sub

Any help would be appreciated, thanks.

Upvotes: 0

Views: 778

Answers (0)

Related Questions