CODEWITHSUNDEEP
excelms-accessvbams-access-2010
braceyourself
braceyourself

Reputation: 257

unable to open MS-Access ADO connection

I am trying to open an Access Database connection using ADODB.Connection object through excel VBA but I am receiving an error when opening the connection.

The error is "Object variable or With block variable not set"

I am using excel 2010, my database is in Access 2010 and I have also added the reference to "Microsoft ActiveX Data Objects 2.8 library"

Any help would be highly appreciated

Dim con As ADODB.Connection

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\vk10084\Desktop\Jobs\PnL\MyDatabaseFiles\Database1.accdb;Persist Security Info=False;"

Edit:

Below is the whole code

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sconString As String
Dim sdbpath As String
Dim sCommand As String

sdbpath = ThisWorkbook.Path & "\Database1.accdb"
sCommand = "INSERT INTO Employees VALUES('Vikas Kumar', '263763')"

Dim cmd As New ADODB.Command

con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\vk10084\Desktop\Jobs\PnL\POC on Access\Database1.accdb;Persist Security Info=False;"


cmd.ActiveConnection = con

cmd.CommandText = sCommand
cmd.Execute

con.Close

Upvotes: 0

Views: 1263

Answers (1)

Martin Dreher
Martin Dreher

Reputation: 1564

I just added a New to the connection and slightly reordered the code to make it more readable. Does this work?

Dim Con As New ADODB.Connection
With Con        
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\vk10084\Desktop\Jobs\PnL\POC on Access\Database1.accdb;Persist Security Info=False;"
    .Open
End With

Dim Cmd As New ADODB.Command
With Cmd
    .ActiveConnection = Con
    .CommandType = adCmdText
    .CommandText = sCommand
    .Execute
End With

Upvotes: 2

Related Questions