HELP! "Please Enter MS JET OLE DB Initialization Information" & Error 1004

[jwms]

Hello,

This is my first thread and first forum Ive posted on. I'm very grateful for everyone who does take the time to respond and use forums to help others. Its most certainly something that amazes me.

Precursor- I don't know all the correct terms, so in my description of problem I'm going to do my best to use the closest terms i know.

My problem is that I have an Excel based "event" that grabs two sets of data each from two different Excel workbooks, and uses some code/ macros in some way, to go get the data, bring it back and enter it into two graphs that compare each parameter WT and DO. So final product is two graphs with two different data sets for each. I did not write it and I don't know a lot about coding, I know some.. Problem is that it worked two months ago, when i tested it for this season, nothing has changed that i can tell... and now when i click "plot" it pops up a window that I have never seen. I don't know who to call or what to even look for. I have checked file locations and paths and everything looks good as far as i can see. I will Copy and Paste part of the code in and point out where the debug is popping the window up. PLEASE if anyone has any thoughts i would be SO grateful. I have a final report that needs to go out technically today, beyond that its ASAP and this graph page is very important for it to be able to go out. Here is the code piece, I also attached a photo of the code on my computer and also the window popping up. THANK YOU so much if anyone looks at this!



With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\My WMS\2017\17 DV\17 DV\Turn1 DV.xls;Mode=Share Deny Write;E" _
, _
"xtended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:E" _
, _
"ngine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLE" _
, _
"DB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale " _
, _
"on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A3"))
.CommandType = xlCmdTable
.CommandText = Array("DORange")
.Name = "Turn1 DV"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\My WMS\2017\17 DV\17 DV\Turn1 DV.xls"
.Refresh BackgroundQuery:=False <-----------------------------------(this is where it pops the window up)
End With
Range("H1").Select
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\My WMS\2017\17 DV\17 DV\Turn11 DV.xls;Mode=Share Deny Write;" _
, _
"Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:" _
, _
"Engine Type=35;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OL" _
, _
"EDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale" _
, _
" on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("H3"))
.CommandType = xlCmdTable
.CommandText = Array("DORange")
.Name = "Turn11 DV"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\My WMS\2017\17 DV\17 DV\Turn11 DV.xls"
.Refresh BackgroundQuery:=False
End With
End Sub

[westconn1]

it would appear it is looking for the password, but afaik you can not use a protected workbook as a datasource unless it is already opened in excel

your connection string should be correct as it has worked previously, but any change to windows or excel versions could generate a problem, though it is a bit different to what i would use

    "Provider=Microsoft.Jet.OLEDB.4.0";"Data Source=" & "C:\My WMS\2017\17 DV\17 DV\Turn1 DV.xls" & ";" & _
"Extended Properties='Excel 8.0;hdr=yes'"

you could test like this separately to see if it opens a connection to your database workbook like

Dim cn As Connection, Rs As Recordset, rs2 As Recordset, cel As Range

Set cn = New Connection
Set Rs = New Recordset
Set rs2 = New Recordset
With cn
    .ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\My WMS\2017\17 DV\17 DV\Turn1 DV.xls" & ";" & _
    "Extended Properties='Excel 8.0;hdr=yes'"
    .Open
End With

if you try this in a different workbook, you would need a reference to ADO

[jwms]

Thank you so much, I will test a few different things with that and get back to you. I cant even express how thankful I am that you spent the time to think about that and try to help me. You must know this coding like a second language huh?

thanks again!

[jwms]

what is ADO btw...

[westconn1]

what is ADO btw...

ActivexDataObjects
to add a reference go menu>tools>references, select microsoft activex data object, make sure to click in the box, not just on the item

[jwms]

Thank you for the help! I havent fixed the problem but I think if i knew just a bit more I could have made your code work. I ended up just going the long way around and finding the data strings i needed to graph and learning how to manually build the two graphs I needed. Got it done and will now work on how to fix this problem for next season. So SO thankful for your time and help. I was really stuck. My last question is if i should put resolved or not...?

[westconn1]

probably not resolved, if you are going to continue trying to fix

it is hard to help with this type of problem, as it is difficult to create the same situation for testing, if you can post the workbooks with some sample data someone may be able to test for you, zip the workbooks before attaching to post