912 questions
Connect to the database using what application? If you're asking how to connect an Access database front end to an Access database back end file, then you can check out the Connect property of the TableDef object.
How do I find my access database connection string?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 53%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELT%3C/text%3E%3C/svg%3E)
Lent, Tom
0
Reputation points
I want to connect to my local access database in VBA. How do I find or make the details in this string that I need to do this connection?
Microsoft 365 and Office | Access | Development
4 answers
Sort by: Most helpful
-
DBG 2,456 Reputation points Volunteer Moderator2025-06-25T16:49:27.5433333+00:00 -
Lent, Tom 0 Reputation points
2025-06-25T17:17:03.8666667+00:00 I'm attempting to automate data update/entry using the VBA editor within Access. Namely generate/modify a query based on a form field (like 'serial number'). I'm assuming a normal way to do this is using ADODB objects. Any time I've done it in the past, I was lucky enough to copy-paste a connection string from some random Google result and change the source to my file name.
So, say I'm using ADODB.Connection.ConnectionString and setting it to "Provider=[howdoIknowwhattheprovideris];Data Source=[insertmyfilehere];Persist Security Info=[doesthisdoanything];"
Where do I get the specific and current information I need to complete this string?
-
George Hepworth 10 Reputation points Volunteer Moderator2025-06-25T18:34:34.3833333+00:00 Please provide the details requested by DBG.
If you mean that you want to connect an Access accdb to tables in a separate Access accdb, that is pretty straightforward. But we need to be sure we're answering the right question.
-
Lent, Tom 0 Reputation points
2025-06-25T18:41:36.72+00:00 I'm using MS Access built-in VBA editor. When attempting to connect, I get the following:
-
DBG 2,456 Reputation points Volunteer Moderator
2025-06-25T20:01:45.2433333+00:00 I think we can definitely help you with that, but let's back a little bit first. Are you trying to modify a query stored in another Access database using your current Access database? Although the connection string is not working, would you mind posting your entire code, please? Thank you.
-
Lent, Tom 0 Reputation points
2025-06-25T20:19:20.3833333+00:00 Everything is in one database and my goal is to have a field where my user can enter a serial number, click/enter/tab/button, and it will populate a table inside the form with rows related to that serial number. I don't have any code beyond the connection.
Private Sub Form_Load()Dim mainCon As New ADODB.ConnectionDim mainCmd As New ADODB.CommandDim mainRec As New ADODB.RecordsetmainCon.ConnectionString = "Provider=TBD;Data Source=RAMAFATA.accdb;Persist Security Info=False;"mainCon.OpenmainCon.CloseEnd SubWhere I have TBD, how do I know what the correct provider is? Aside from just asking someone who might already know. Every search result gives me a different answer and it's rare that any of them work. Google "access database provider" and all I get is companies who provide databases.
-
DBG 2,456 Reputation points Volunteer Moderator
2025-06-25T20:39:30.8766667+00:00 Usually, the provider is the RDBMS system you're trying to connect to. For example, if it was a MS Access database, the provider might look something like: "Provider=Microsoft.ACE.OLEDB.12.0". However, it's possible you might not even need all that if you could tell us the big picture. Are you trying to display a bunch of records in a subform or a continuous popup form based on the text entered by the user? Is that data already available within the same database file?
-
Lent, Tom 0 Reputation points
2025-06-30T22:27:01.3766667+00:00 DBG's comment above answers the spirit of my question. Namely: "Provider=Microsoft.ACE.OLEDB.12.0" and thanks for that. But the intent of my question is, how did you know that? Or, what can I do next time aside from fishing around forums and help sites until I'm lucky enough for someone to provide what they already know?
-
DBG 2,456 Reputation points Volunteer Moderator
2025-06-30T22:36:02.12+00:00 As I said earlier, the value you want/need depends on the system you're trying to connect to. There is a published list for most of them at connectionstrings.com. But the other point I was also making was that you don't always need to use an ADO connection to work with your data. Cheers!
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(243.2, 5%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDF%3C/text%3E%3C/svg%3E)
Dale Fye 0 Reputation points2025-06-25T16:53:54.78+00:00 And if you don't already have that link, you can create it with the
External Data => New Data Source => From Database => Access
option on the ribbon.
-
Ken Sheridan 2,851 Reputation points2025-06-30T21:06:05.85+00:00 The following is a code snippet which creates a temporary external database and creates two tables in it, then creates links to those tables in the current database. You should be able to extract examples for necessary code for what you want to do from this:
' create temporary database in same folder as current database ' or return reference to database if already exists strTempDb = Left$(dbs.Name, Len(dbs.Name) - Len(Dir(dbs.Name))) & "BoMTemp.accdb" On Error Resume Next Set dbsTemp = CreateDatabase(strTempDb, dbLangGeneral) If Err <> 0 Then Set dbsTemp = OpenDatabase(strTempDb) End If On Error GoTo Err_Handler ' create BoM table in temporary database if doesn't exist strSQL = "CREATE TABLE BoM (MajorPartNum LONG NOT NULL, " & _ "MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL, " & _ "CONSTRAINT MajorMinor PRIMARY KEY (MajorPartNum, MinorPartNum) )" On Error Resume Next dbsTemp.Execute (strSQL) On Error GoTo Err_Handler ' create BoM_Temp table in temporary database. ' note that this one has no constraint as it will be ' necessary to add duplicates of major/minor part number ' values in separate rows strSQL = "CREATE TABLE BoM_Temp (MajorPartNum LONG NOT NULL, " & _ "MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL)" dbsTemp.Execute (strSQL) ' check if link to BoM_Temp table exists and if not create link On Error Resume Next Set tdf = dbs.TableDefs("BoM_Temp") If Err <> 0 Then DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb, acTable, "BoM_Temp", "BoM_Temp" Else varFld = tdf.Fields(0) If Err <> 0 Then ' refresh link if current link invalid tdf.Connect = ";DATABASE=" & strTempDb tdf.RefreshLink End If End If Err.Clear ' check if link to BoM table exists and if not create link Set tdf = dbs.TableDefs("BoM") If Err <> 0 Then DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb, acTable, "BoM", "BoM" Else varFld = tdf.Fields(0) If Err <> 0 Then ' refresh link if current link invalid tdf.Connect = ";DATABASE=" & strTempDb tdf.RefreshLink End If End If Err.ClearYou'll find the code in Bom.accdb in Bom..zip in my Dropbox public databases folder at:
-
Ken Sheridan 2,851 Reputation points
2025-06-30T21:11:46.0666667+00:00 For means of browsing to and opening a file from within Access take a look at BrowseDemo.zip in the same Dropbox folder to which I gave you a link in my last reply. Oops. That was meant as a reply to another thread.