VB - Connecting to an Access DB

December 2016




Issue


I have a table Excel I want to copy a DB in Access when I click a button on the sheet. Xls containing this table.

For now I'm testing the connection and the creation of the table. My code looks like this:


Sub cnxBDD ()  

string = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C: \ Users \ Nant \ Desktop \ MABDD.mdb; Persist Security Info = False"  

Set DB = New ADODB . Connection  
Set recSet = New ADODB.Recordset  
BDD.Open CSQL  
Me.Refresh  


Sun DB As ADODB.Connection  
Sun recSet As ADODB.Recordset  
Sun CSQL As String  

BDD.Execute "CREATE TABLE test (name varchar (60), FirstName varchar (60 ), mail varchar (60), Nickname varchar (60), DateAjout date not null)  


"End Sub 


The error that occurs is
this:" ADO: Compile Error: type user-defined not defined "and on "New ADODB.Connection"

Solution


It should add reference Microsoft ActiveX Data Objects xx Library project.

In the menu of the VB editor => Tools => References ... tick correspondate.

Then you need to declare the objects before creating them ...


DB As ADODB.Connection Sun Sun Sun recSet As ADODB.Recordset CSQL As String Set DB = New ADODB.Connection Set recSet = New ADODB.Recordset 'The connection string str = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C: \ Users \ Nant \ Desktop \ MABDD.mdb; Persist Security Info = False " 'We created the connection string BDD.Open' We wrote the CSQL query =" CREATE TABLE test (name varchar (60), FirstName varchar (60 ), mail varchar (60), Nickname varchar (60), DateAjout date not null) " 'We pass the query to the database recSet.Open CSQL, DB,,, adCmdText' It closes the connection and release objects on BDD. Close recSet.Close = Nothing Set DB = Nothing Set recSet




It should work!


Also check this tip about connecting VB to Access database:

http://ccm.net/faq/6079-connecting-vb-to-database

Note


Thanks to Polux31 for this tip on the forum.

Related :

This document entitled « VB - Connecting to an Access DB » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.