VBA Prevent MS Access Database Engine Msgbox

[Pinaceous]

Hello Everybody,

I'm importing a xlsx file where if the file is not present, a MsgBox automatically pops:

Please Enter Microsoft Access Database Engine OLE DB Initialization Information

Does anyone know how I can prevent this MsgBox from popping up?


Thanks,
Pinaceous

 

[Kenneth Hobson]

I guess check if it exists before doing it? Dir() and fso.FileExists() are two methods. e.g.

Sub Test()
  If Dir("c:\temp\ken.xlsx") <> "" Then
    MsgBox "Do stuff..."
    Else: MsgBox "Don't do stuff..."
  End If
End Sub

 

[Pinaceous]

Hi Kenneth,

Thank you for posting!

In trying

Sub Test

it does not seem to work, unless I specify which specific user folder that I'm referring to.

In exploring this I've ran into https://www.connectionstrings.com/ace-oledb-12-0/ which tells me that I might need more to

Sub Test

to make it function for this particular case.

This is strange because the code itself doesn't ask me for a user name.

Here is the code that I'm working with, which maybe you can tell why:

Sheets("Worksheet5").Select
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Desktop\ken.xlsx;Mode=Share Deny " _
        , _
        "Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet " _
        , _
        "OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" _
        , _
        "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy " _
        , _
        "Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;" _
        , _
        "Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
        ), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("'Inbound Flight$'")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Users\Desktop\ken.xlsx"
        .Refresh BackgroundQuery:=False
    
    End With

Thank you!
Pinaceous

 

[Kenneth Hobson]

I have not seen a connection string that long.

I am not sure that this even works. It does show the concept that I explained and a bit more. You may need to concatenate the fn in your code as I did if it does not "work".

Sub Test()
  Dim fn$, s$, ws As Worksheet, a(1 To 7)
  
'Inputs
  fn = CreateObject("WScript.Shell").specialfolders("Desktop") & "\ken.xlsx"
  s = "Worksheet5"
  
'Exit if fn does not exist.
  If Dir(fn) = "" Then
    MsgBox fn, vbCritical, "Macro Ending - File Does Not Exist:"
    Exit Sub
  End If
  
'Exit if worksheet "s" does not exist.
  If WorkSheetExists() Then
    Set ws = Sheets("Worksheet5")
    Else
     MsgBox "WorkSheet: " & s, vbCritical, "Macro Ending - WorkSheet Does Not Exist:"
     Exit Sub
  End If
  
  a(1) = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" & _
          "User ID=Admin;Data Source=" & fn & ";Mode=Share Deny "
  a(2) = "Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";" & _
          "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet "
  a(3) = "OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;" & _
          "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;"
  a(4) = "Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;" & _
          "Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy "
  a(5) = "Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;" & _
        "Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;"
  a(6) = "Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;" & _
        "Jet OLEDB:Bypass ChoiceField Validation=False"
  a(7) = "Destination:=" & ws.Range("A1")
  
  With ws.ListObjects.Add(SourceType:=xlSrcExternal, Source:=a).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("'Inbound Flight$'")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = fn
    .Refresh BackgroundQuery:=False
  End With
End Sub

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

 

[Pinaceous]

Hi Kenneth!

I have not seen a connection string that long.

I totally hear you on that one!

I really do appreciate that you provided a code for me to test out.

Let me try it out and I'll repost later on.

Thanks again!
Pinaceous

 

[Pinaceous]

Hi Ken,

I'm getting a MS VBA MsgBox popup:

Compile error: Ambiguous name detected: WorkSheetExists

It is highlighting:

Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean

To be clear, where would one place the above code?

Is it in the same module as the running sub or under the Microsoft Excel Objects for that Sheet?

Thank you!
Pinaceous

 

[Kenneth Hobson]

Put it in only one Module.

I seldom use Select, Selection, and Activate. This is especially so when coding in a Sheet/Worksheet object. Notice how I more explicitly qualified the sheet object.