Query returns no records (does in excel)
I have setup a Word 2013 macro to fill a combobox from an iseries (HTE-sun) data-source, the query works from excel and fills the spreadsheet - macro works fine.
I am attempting to setup to pull the data live directly from the AS400 -- query runs without error but returns no records (.recordcount=-1^)
--- The basic code I used is:
Set rsPI110AP = New ADODB.Recordset
Set cnPI110AP = New ADODB.Connection
Dim sqlPI110AP As String
Dim strTemp As String
Dim i As Long
cnPI110AP.ConnectionString = "DSN=AS400; Uid=********;Pwd=**********"
sqlPI110AP = "SELECT PI110AP.PIIN, PI110AP.PIID1, PI110AP.PIID2, PI110AP.PIUOMO FROM S1072EEF.HTEDTA.PI110AP PI110AP ORDER BY PI110AP.PIIN"
rsPI110AP.Open sqlPI110AP, cnPI110AP, adOpenStatic, adLockReadOnly
I've been apart from AS400 a few years so I may be missing something --
What do I need to do to correct this construct?
Any help would be most appreciated. Thank you
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 Dinos2 days 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 Cykowski2 weeks 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 Khan1 month 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 avery3 months ago
Hi All, can anyone provide connectionstring for Excel 2010. I tried to set connection string for RPA Automation anywhere but it is throwing an error0 votes1 answerSiva Shanker4 months ago
Connect with ODBC ( Power Query Excel)
I'm trying to connect ODBC database by excel power query, but it's not works. When I try in Windows 7, its works, but in Windows 10, I'm facing problem.0 votes0 answersAnonymous user5 months ago
Excel 2016 VBA OLE SQL connection help
Excel 2016 VBA to create an OLE connection to a sql 2014 db and then execute a stored procedure returning the results starting cell A1 on sheet :StoredProcData. Server & instance is QGZZZ\MYINSTANCE db is data0 votes0 answersRichard5 months ago
Excel data source as Stream instead of physical path
I have a connection string as below. connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;ISAM=1;TypeGuessRows=0'";0 votes0 answersJERIN ANTONY10 months ago
Connection error to SQL in Excel 2007
Hi, I have connected a Excel 2016 file to a SQL Server and it works ok. An user, who has Excel 2007 version should use the same file and he is reporting this error while refreshing the data: Run-time error '1004': Application-defined or object-defined error.0 votes0 answersJ Jesus Romero10 months ago
iSeries Access ODBC Driver connection query returns empty recordset
iSeries Access ODBC Driver connection query returns empty recordset if library is not specified. Is there a way in which I can use this string to connect to ISD (As400) and quesry without specifying a library?0 votes0 answersMahlatse Dauon May 11, 2018
- Load more posts