1
Good afternoon, everyone.
I created a database to control the receipt and delivery of cards. The inclusion of the data is done through a form in excel.
This form also has the option of consulting existing data. query returns the information in the form itself for screen view.
My problem is that if there is more than one record it will not be possible to check all of them on the form. My exit then was to create a kind of report. When you click query first a function is called that counts how many records the query will return. If more than one is created an excel file where the records would be saved. However the Insert Into Openrowset function is showing error.
I make available below the codes. If someone can give me a light I thank.
This code is the counter. it checks in the BD how many records are compatible with the search criteria.
Public Function Contador()
Dim TOTAL As Variant
Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"
cn.Open
Set rs = New ADODB.Recordset
sql = "SELECT COUNT (*) FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "';"
rs.Open sql, cn
If Not rs.EOF Then
Do While Not rs.EOF
TOTAL = rs(0)
rs.MoveNext
Loop
End If
cn.Close
Contador = TOTAL
End Function
If the value returned is greater than 1 (Counter>1) then a function is called that creates the xls file:
Public Function CriaArquivo()
Dim NovoArquivoXLS As Workbook
Dim sht As Worksheet
Dim mPathSave As String
Dim PlanName As String
mPathSave = ThisWorkbook.Path
PlanName = "SQLQueryControleCartoes"
'Cria um novo arquivo excel
Set NovoArquivoXLS = Application.Workbooks.Add
'Salva o arquivo
NovoArquivoXLS.SaveAs mPathSave & "\" & PlanName & ".xls"
Call Cabecalho
End Function
The Create file function in turn calls another function that inserts the header:
Public Function Cabecalho()
Dim vArray As Variant 'variável insere dados vArray
Dim vContador As Integer
' variavel vArrays variant com array de dados
vArray = Array("", "ID", "TP_BENEFICIO", "BP", "CPF", "NOME", "DTADM", "FILIAL", "SOLICPOR", "DTSOLIC", _
"DTRECEBE", "DTENVIOBS", "ENVIADORETIRADO", "NMMINUTA", "NRCARTAO")
'Inserindo o cabeçalho na folha de planilha
With Worksheets("Planilha1")
For vContador = 1 To UBound(vArray)
.Cells(1, vContador).Value = vArray(vContador)
Next vContador
End With
End Function
After all this process is called the function to insert the data in the spreadsheet:
Public Function Relatorio()
Dim sql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rel As String
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & enderecoDB & ";Jet OLEDB:Database"
cn.Open
Set rs = New ADODB.Recordset
sql = "INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database= " & ThisWorkbook.Path & "\SQLQueryControleCartoes.xls', 'SELECT * FROM controle WHERE BP = '" & controlectform.nmbpbox.Value & "')"
rs.Open sql, cn
End Function
All codes are executed normally. The file is created and the header inserted. But when entering the results of the query is presented the error below:
The error is pointed out in the penultimate line of the report function where the "rs. Open sql,cn"
http://stackoverflow.com/questions/25753278/insert-into-openrowset-syntax-with-dynamic-t-sql
– Reginaldo Rigo
@Reginaldorigo thanks for the link. I tried to do as shown there but still showing error. Set sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''Excel 12.0;Database='U: CSU TAM Control Cards database Sqlquerycontrolecartoes.xls''',''''SELECT * FROM [Planilla1$]') SELECT * FROM control WHERE BP = '''controlform.nmbpbox.Value''''' A message appears saying "Error of Compilation! It was expected: Expression"
– Humberto Faria
I’d say those quotes aren’t balanced.
– Reginaldo Rigo
I looked at the quotation marks, wrote the code and compared it with the code available in the link above. Still presented error. I searched some more and developed the code like this: sql = "INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=U: CSU TAM Control Sqlquerycontrolecartoes.xls;', 'SELECT * FROM [Planilha1$]') SELECT * FROM control WHERE BP = " & controlectform.nmbpbox.Value & ";" Still error. Now it says that the OPENROWSET output table could not be located.
– Humberto Faria
See this other link. http://stackoverflow.com/questions/909933/sql-server-export-to-excel-with-openrowset
– Reginaldo Rigo
Reginaldo, I thank you for your help, but you keep making a mistake. The code available in Lik above displays the same message as the first "Build Error! It was expected: Expression", I reviewed the whole structure and rewritten the code, even then the error persists.I also tried to use the Docmd.Transferspreadsheet method but also gives error. It says that the "Transferirplanilha" method is not available.
– Humberto Faria