Estou experimentando usar o ADO para conectar um frontend do Access a um backend do Access. O frontend é apenas uma ferramenta de entrada de dados. Os usuários não farão edições/atualizações em nenhum registro no backend. O motivo para fazer isso é que mais de 255 usuários usarão o frontend simultaneamente, então tabelas vinculadas normais com conexão persistente ao backend não funcionarão (sei que essa configuração parece ilógica; por favor, evite dizer que eu não deveria usar o Access para o backend).
Consegui fazer funcionar para algumas coisas. Uma Insertinstrução SQL curta funciona como esperado, mas uma Insertinstrução mais longa (11 colunas no meu caso) resulta em um erro:
Erro de tempo de execução '-2147217900 (80040e14)': erro de sintaxe na instrução INSERT INTO
Veja como o código se parece:
Public Const ConnectionStr As String = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\\[Network]\[Folder]\[File].accdb"
Dim conn As New ADODB.Connection, SQLstr as String
conn.Open ConnectionStr
SQLstr = "INSERT INTO [mytable](field1, field2, [...], field11) values(1, 2, [...], 11)"
Debug.Print SQLstr
conn.Execute SQLstr
conn.Close
Os valores na insertinstrução são todos obtidos de controles no frontend, então há muita concatenação de strings, mas esse não é o problema. Copiei a debug.printsaída da janela imediata, colei na janela de consulta do Access, executei e o registro foi inserido sem problemas.
(Eu também estava recebendo "erro de sintaxe na cláusula FROM" ao fazer uma consulta select com uma cláusula where, mas não consigo replicar isso agora)
Então, qual é o motivo desse erro e existe uma maneira de corrigi-lo? Existe um limite de tamanho de texto para strings SQL executadas com conexões ADO? Acho que isso não faria sentido... Mas testei reduzir o número de campos na instrução insert para 9 e funcionou.
Minha solução alternativa é apenas usar o DAO, criando uma tabela vinculada temporária ao backend, executando a insertinstrução e, em seguida, excluindo a tabela vinculada
Imagem adicionada em resposta à resposta de Jonathan abaixo: captura de tela do VBE
