Search results

I will try that - good idea - but I suspect so. I need to gain more access to SQL server database before testing more (I was looking over a screen share so far).

Hello - connecting to an SQL server (sitting on Azure) and wanting to extract an XML field, using VBA, and ultimately parse it. Having trouble even grabbing the field. The prior programmer used ADO, but I'm more comfortable with DAO. It seems the connection is working (using ADO, but again I may...

Yeah - I was hoping to leverage ADO only because when I've done THOUSANDS of query updates, ADO is faster in that case. I'm guessing the time difference for just 2 records however is not worth losing the convenience of DAO/query updates.

Is it possible that running Access 2016 is part of the issue? If I update to say Access 2019 on the terminal server, that might lessen the issue?

Re: reading a specific record, I can grab a recordset with DAO easily enough matching certain criteria, e.g., I create a recordset as I please so I can loop through specific records, and I call that recordset using this: Function fnServerRecordset(pSQL As String) As DAO.Recordset Dim qdf...

Re: filling in a local table, using DAO/linked table, I often run a query that grabs the SQL server table data, and inserts into the local table, in one fell swoop, e.g., see below. Can't do that in batch in ADO? Has to be record by record? Imagine that would be slower then... sql="SELECT *...

Thanks - client side is Access, but I want to connect to SQL server via ADO. Also, is there quick coding to take a SQL table and insert that data into a local access table using ADO? Maybe a related question is... can you link tables into Access via ADO versus DAO?

Anyone please have sample coding to get me started doing the following using ADO: >> Connecting to SQL server table >> Reading records in a loop >> Reading a specific "query" record >> Updating a record THANKS!!! I googled a lot but can't find a comprehensive / user-friendly site (at least not...

I hear you - IT is still trying but no luck so far. Ugh.

I'm using a remote server so that shouldn't be an issue since the remote server and the SQL server are all on the same Company network. Also, I tried a "keep alive" subroutine that runs every 5 records... to reconnect one of the ODBC tables. Still not helping.

BTW, when the error hits, if I just CONTINUE (F5) the VBA, it keeps working! Infuriating!

Sorry to post again but just checking if someone else has run into this... I have linked tables via ODBC. The connection works fine for 10 to 15 mins, then randomly shuts down. Once I click "cancel" on the error, the coding picks right back up again (doing a batch process). IT has tried fixing...

I would agree that it seems the linked tables remain (of course), Access isn't adept at re-connecting. Or catching a mis-connection.

OKay more info. Getting sql server error 10054. I'll also google in the meantime. --------------------------- Microsoft SQL Server Login --------------------------- Connection failed: SQLState: '08001' SQL Server Error: 10054 [Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing...

Hi - have you ever had a random disconnect to SQL server? I've gotten that but MORE relevant now, it seems our tech guys have implemented a time-out feature to disconnect ODBC connections after some time. Is there a way to "catch" such a disconnect and force a reconnection especially when...

Yeah for end users I'm trying to avoid that. I re-tried putting Trusted_Connection = Yes in the string and so far seems to be working, so maybe just a fluke.

I use connection strings to connect to SQL server and attach tables. For a while I used "SQL Server" such as: ODBC;Driver={SQL Server};Server=SERVER-NAME;Database=DATABASE-NAME;Uid=USER-NAME;Pwd=PASSWORD;Encrypt=No;Trusted_Connection=No This worked just fine. I could refer to the tables in...

Yes - I'm building a complex report in Excel and grabbing different data elements as needed. If ADODB lookup is 50% faster (0.25 seconds versus 0.5 seconds), that would add up I guess.

Hello - I often, for a quick data point lookup, use DAO, such as: Dim sql as string, qdf As DAO.QueryDef, rst as DAO.recordset sql = "SELECT BirthDate FROM tblPerson WHERE Name = 'John'" Set qdf = CurrentDb.CreateQueryDef("") qdf.Connect = CONNECTION_STRING qdf.ReturnsRecords = True qdf.sql =...

P.S. Maybe I don't have the right Tool references either? The database worked fine prior to 64-bit.