Search results

I can grab other fields from the table, but any XML type field gets truncated, and not always at 18 characters. It varies.

Okay here's my new issue ... when I grab the XML field from SQL server, it only grabs the first 18 characters, whereas I know it's much longer. I'm using a String type in VBA. xmlstring = rst("XMLField") EDIT: It is grabbing the last 18 characters of the XML field (last 2 lines). Why won't it...

Actually now it's working - THANKS!!!

I get this error using that coding... 3305: Invalid connection string in pass-through query

Thanks. How about any suggestions on Connection Strings for an Azure SQL server database, from Excel? THANKS.

Okay I have access to the SQL server database now. I'm trying to set a recordset = a passthrough query on the server, but in Excel VBA (not Access VBA). Any coding examples? This is what I would use in Access... Function fnServerRecordset(pSQL As String) As DAO.Recordset Dim qdf As...

Also, for connecting to sql server on Azure, any suggested connection strings that work well for you? Thanks.

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.