Excel VBA connecting to Access Database

[Jas]

Hello, I have code like this in my module:

Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;"
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

Set Command = New ADODB.Command
Command.ActiveConnection = Connection

'Insert records into Work_Data table
Command.CommandText = "INSERT INTO Work_Data " & _
"VALUES (@Team,@Case_Owner,@Date,@Week_Beginning,@HO_Ref,@Finished_Work)"

For Each rngDate In Union(Range("Mon_Date_Data"), Range("Tue_Date_Data"), Range("Wed_Date_Data"), Range("Thu_Date_Data"), Range("Fri_Date_Data"))
For Each rngHORef In Range(rngDate.Offset(9, -1), rngDate.Offset(23, -1))
If rngHORef <> "" Then
With Command
.Parameters.Append .CreateParameter("@Team", adVarChar, adParamInput, Len(Range("Team").Value), Range("Team").Value)

Please can you suggest books and or other training material to learn about connecting and coding for accessing the database?

 

[NARAYANK991]

Hi ,

You may find these useful :

http://oreilly.com/catalog/integratingea/chapter/ch03.pdf

http://vbadeveloper.net/portfolioreportingexceltosqlvba.pdf

Narayan

 

[Jas]

Thanks for your suggestions Narayan.

 

[Jas]

For Each rngDate In Union(Range("Mon_Date_Data"), Range("Tue_Date_Data"), Range("Wed_Date_Data"), Range("Thu_Date_Data"), Range("Fri_Date_Data"))
For Each rngHORef In Range(rngDate.Offset(9, -1), rngDate.Offset(23, -1))
If rngHORef <> "" Then
With Command
.Parameters.Append .CreateParameter("@Team", adVarChar, adParamInput, Len(Range("Team").Value), Range("Team").Value)

Hello,

In the code above, after the last line I would like to insert a value held in a variable into my table. Is this possible without writing my variable to a cell in my excel sheet and then reading it back into my macro.

Thank you

 

[SirJB7]

Hi, Jas!
Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from th right excluding the last and separated one.
Regards!

 

[Jas]

Hi, Jas!
Whenever you post code, you should embed it within proper tags to preserve indentation and spacing, as it's displayed just above the reply text box in this page used for posting comments. Or using the related icon from the ribbon toolbar, 5th from th right excluding the last and separated one.
Regards!

Thank you. This is now resolved. I read about the parameters for .Parameters.Append .CreateParameter command and then, with a bit of trial and error, managed to code the required line.