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
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)
The error that occurs is
this:" ADO: Compile Error: type user-defined not defined "and on "New ADODB.Connection"
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:
Thanks to Polux31 for this tip on the forum.