This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](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)
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?
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.
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?
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.
I'm using MS Access built-in VBA editor. When attempting to connect, I get the following:
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.
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.Connection
Dim mainCmd As New ADODB.Command
Dim mainRec As New ADODB.Recordset
mainCon.ConnectionString = "Provider=TBD;Data Source=RAMAFATA.accdb;Persist Security Info=False;"
mainCon.Open
mainCon.Close
End Sub
Where 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.
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?
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?
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!
![](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)
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.
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.Clear
You'll find the code in Bom.accdb in Bom..zip in my Dropbox public databases folder at:
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.