wisenhiemer wisenhiemer - 1 year ago 112
SQL Question

Append queried Recordset to Table in Access

I am querying Active Directory to list Users and other fields in Access. Is there a way to append my queried results into an existing table? Currently I am trying to use INSERT INTO but having issues with my Object variable not being set or block variable.

Private Sub Command0_Click()

Dim objRecordSet As Object
Dim objCommand As Object
Dim objConnection As Object
Dim dbs As Database


Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.Properties("Sort On") = "whenCreated"

objCommand.CommandText = _
"SELECT Name,Title,PhysicalDeliveryOfficeName,WhenCreated,Mail FROM 'LDAP://OU=Standard Users,OU=Active Users,OU=All Users,DC=contoso,dc=local' WHERE objectCategory='user'"
Set objRecordSet = objCommand.Execute

Do Until objRecordSet.EOF
dbs.Execute " INSERT INTO ADUsers" & "(Name,Title,Site,Created,Email) VALUES " & "(objRecordSet.Fields('Name').Value,objRecordSet.Fields('Title').Value,objRecordSet.Fields('physicalDeliveryOfficeName').Value,objRecordSet.Fields('whenCreated').Value,objRecordSet.Fields('Mail').Value);"
Debug.Print objRecordSet.Fields("Name").Value; "," & objRecordSet.Fields("Title").Value; "," & objRecordSet.Fields("physicalDeliveryOfficeName").Value; "," & objRecordSet.Fields("whenCreated").Value; "," & objRecordSet.Fields("Mail").Value



End Sub

Answer Source

Everything inside doublequotes " is interpreted as string not as code and strings (the values of objRecordSet.Fields("myFieldName").Value) have to be quoted in insert statement.

dim strSQLInsert as String

strSQLInsert = "INSERT INTO ADUsers(Name,Title,Site,Created,Email) VALUES ('" & _ 
  objRecordSet.Fields("Name").Value & "','" & _
  objRecordSet.Fields("Title").Value & "','" & 
  objRecordSet.Fields("physicalDeliveryOfficeName").Value & "','" & _
  objRecordSet.Fields("whenCreated").Value & "','" & _
  objRecordSet.Fields("Mail").Value & "');"

Debug.Print strSQLInsert

dbs.Execute strSQLInsert

Store your sql statements in a string, then you can check it with Debug.Print.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download