Excel 2010 vba loses system DSN connection
I have been successfully using the following PostgreSQL connection string in multiple Excel 2010 vba applications running on 64 bit Windows 7 systems. I have moved to a Window 10 system and my application will randomly lose it's connection to the DSN. It may take 5000 calls one time and then 1500 calls the next. I am using a unicode driver.
I wrote a sample program that makes the same call in a loop. The code will randomly fail on Window 10 and run flawlessly on Window 7.
ODBCDSN = "ODBC;DSN=PostgreSQL35W;"
Dim i, k As Integer
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
Dim sLastJob As String
Dim iJump As Long
Application.DisplayAlerts = True
iMaxParts = 5000
sConn = ODBCDSN sConn = sConn &
Application.ScreenUpdating = True
iJump = 1
For i = 1 To iMaxParts
sLastJob = sPartUsage(i, 8) ' Last Job
sSql = "" sSql = "SELECT job.ccmasterid,estimateactivity.cost, estimateactivity.markup,estimateactivity.hours,estimatequantity.quantityordered,estimate.lastjob,estimate.customerid,estimate.entrydate,jobpart.esmasterid,jobpart.ccjobpart, " sSql = sSql & "jobpart.ccqtyordered,job.ccdatesetup,estimateactivity.unitlabel " sSql = sSql & "FROM ( ( ( ( ( public.estimatequantity estimatequantity INNER JOIN public.estimatepart estimatepart ON " sSql = sSql & "estimatequantity.estimatepartid=estimatepart.estimatepartid ) INNER JOIN public.estimate estimate ON estimatepart.estimate=estimate.estimateid ) " sSql = sSql & "INNER JOIN public.estimatestatus estimatestatus ON estimate.status=estimatestatus.id ) INNER JOIN public.jobpart jobpart ON " sSql = sSql & "estimate.estimatenumber=jobpart.esmasterid ) INNER JOIN public.job job ON jobpart.ccmasterid=job.ccmasterid ) INNER JOIN " sSql = sSql & "public.estimateactivity estimateactivity ON estimatequantity.estimatequantityid = estimateactivity.estimatequantityid " sSql = sSql & "WHERE ((job.ccmasterid = '37712')) AND ((estimatestatus.sequence = 7)) AND ((estimateactivity.activitycodeid = '70515')) AND " sSql = sSql & " ((estimateactivity.unitlabel LIKE '%Warehous%')) AND ((estimatequantity.quantityordered = jobpart.ccqtyordered)) " sSql = sSql & "ORDER BY job.ccmasterid, estimatequantity.quantityordered"
' MsgBox sSql
Set oQt = ActiveSheet.QueryTables.Add( _
Destination:=Range("a" & iJump), _
iJump = iJump + 2
Excel 2016 OLDBConnection opening Password protected sheet.
I need to know how to open a password protected Excel file using OleDBConnection. I saw a post that provides a work around. I am using Excel 2016. Is there a way in this version? The excel is 2013 and older.0 votes0 answersIsaac Koenig2 weeks ago
remote connect on a wifi network
Hi, my name is Marvin. I have little experience in mysql I have mysql 5.5 installed with workbench 6.1. I work with vba excel developing small applications, locally I have no problems connecting to databases.0 votes0 answersMarvin Rosal1 month ago
ADO connection string to a shared Excel workbok on Sharepoint
Can I use ADO to open a connection to a shared Excel (Office 365) workbook that resides on SharePoint. I do not want to checkout the work book as Other people are storing information on it.0 votes0 answerszubair Aleem2 months ago
Oracle ODBC x86 and x84 on Excel 32bits.
I have the Oracle Runtime 11g 32 bits and the 12c Runtime 64bits instaled on my system, and i would like to know if is possible to use the 32bits Oracle 11g(18.104.22.168) ODBC driver on my Excel.0 votes0 answersLucas Pott3 months ago
Connection string properties
Hi, I use a OdbcConnection string to read xls sheet into a data table (VB.net) is there a property I can use to omit formulas (ex. Vlookup) and use the values instead?0 votes0 answersjos Clement3 months ago
Loading dbf Foxpro file from Excel VBA Project
Hi there: I’m trying to load a dbf file generated from Foxpro7 into Excel (programmatically), I don’t want to use any export/import mechanisms because the objective is to carry out this transparently for the user.0 votes1 answerKlueze6 months ago
VBA update sharepoint inserts
I think I'm almost there. Except the update is inserting into sharepoint. Using Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0. See code below. Situation: I'm refreshing in an excel table from a power query a Sharepoint list (Sharepoint server 2016).0 votes3 answersThe Dinos6 months ago
Need connection string for Office 2019
I wrote a VB .NET program using VS17 that opens Excel files and loads the data into an Access database. For .xls files, I use the connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ...;Extended Properties Excel 8.0;1 vote2 answersMark Cykowski7 months ago
Excel VBA ERROR ODBC connection failed
Hi, it is working in Excel-32 bit version, but not working in excel-64 bit. I have a linked table in ms-access which is linked to a sql server table, and when I am trying to fetch the data in excel via VBA from ms-access linked table the error message "ODBC connection failed" showing.0 votes1 answerKashif Khan8 months ago
[Microsoft][ODBC Driver Manager] Data source file not found and no default driver specified.
I have a VBA code (in excel 2016) that i use to grab information from a MS SQL server (2008R). The connection works fine on the computer i wrote it on. When i run it on another computer i get the error "[Microsoft][ODBC Driver Manager] Data source file not found and no default driver specified".0 votes1 answerlee avery10 months ago
- Load more posts