Alexandre Fim 22/4/2022, 23:05
Magnus,
Fiz um exemplo de formulário para carregar o dia da semana através de uma function no SQL Server.
Ao informar a Data e clicar em "Executar", o sistema carrega o dia da semana:
![[Resolvido]Executar uma function do SQL Server no MS ACCESS D+ra5tDbQ8xfgAAAABJRU5ErkJggg==](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfcAAACjCAYAAACXHVdyAAAYaklEQVR4nO3dfZRbZYHH8W8ssLysHhV5UUZESOiptCBVDk1mWZcitjNUaccyu5W1YIuZKkJnji0CitNSLEjRyVDRTpZWqMLszmI7aEnauqB7OJ3UhS1uW+0ZEl7UYQq6e2RdaDszyX32jySTm5lkMi+ZZHL7+5xzD82993lJhjO/PC/JuHp7ew0iIiLiGO8odwdERESkuBTuIiIiDnNC+h/PLFpczn6IiIjIBM3d/gRgC3eA6/fuKUtnREREZGIem1M9+G9Ny4uIiDiMwl1ERMRhFO4iIiIOo3AXERFxGIW7iIiIw5xQ+JaRdQe2YSUSAFgDCRL9A8mjr5943wCJ/n7iff0k+gaI9w9g9cdT98axEgkW7P7uRLsgIiIiNhMOd4BTpp8DgEkksAYSmHicRH8c0zeA1Rcn0T+A6R8g0RfHiifD3QwkiPybPnonIiJSbEUJ91d+sAuAhLGwjCGRiBM3FvFEggErTtyyiCfixK0EA5aVvNdKAPpaexERkWKbcLi/MP1D/ENj3bDzxiSj27LAMpCwIG4ZBhLJa4mExYIlDdwy0Q6IiIhMEddeey3Nzc3Mnj075/V9+/axdu1annzyyUntx4Q31MXT0+wmMwq3LIgnoH8AjsXhSL/hrT7DW33w9jE40gfRl383pnZ+cft0pk8fftRtfnWiT0FERKQompubWbVqFfv27Rt2bd++faxatYrm5uZJ78eER+4D8QEAvrnxGQD6+97iyFtvcuTIn3nwnpV8u+0Z+vve5tjR/+XI22/Sd+z/WPv1O8bczpX3ddN9H8AvuH36Rjy7trH8vLHWMpGyIiIiI5s9ezYPPPAAq1at4oEHHhgcwaeD3X5uMk043OMDyXC/+5a5gItEago+kYAByxCP9/O5a2dz2iknc9qpf8Xq5o3ELa21i4iIMw0NeKCkwQ7FCPfUtDxAwiSn5NPr6wnLxUD/Ub63+UkS8TjxeD/GGOKWiyP9RfyI/aubqZt3P78BYBGbuu/jSl5lc9087k+eZNGmTbBiBdsB5k3n/kWb6L7vyjxlRURExs8e8EBJgx2KtFt+JLd9eREDCRhIJNfhBxLFbuEX3D7vKa7Z1c2284Bf3E7d5le58vxN3H/hJrq32aK6exNkTcvnKas5exERqWBFC/fMmvvbHD3yJkfffpNvN9/Kxkeeob/vCEePvMmxI2/Sd+wtbv/a7cVqFl59mRf5DdvnTef+9LlF8+AqDxdtX0GdZxfb8oV1vrIo3EVEZPzsa+xQgdPyiUQCYwx33zKXActFImGIWySPhCE+0M/1n7mUU089mdNOPZnVzQ+SsAx9A8Vcd881nX4l27qX8+rmOqZP/w2LNnVzX875dk3Fi4hI8eTaPJdrk91kKtrC94DlSq23u0gkkkc8AfH4UTZueZL1gcf42t0/GFxzL5rzzudCtrMxz0fizlu+jV23XcSLL+e4XqCsiIjIWOTbFW9fg8/1MbliK+qae8JKbqqLm+T6etyCL31hEXErs+ae3GhXzFH7ldy36zbq5s1jenpufdEmuuftYvqK7ekTbOo+DziPeYtWsMK2oS5n2dxDfBERkRGtXbs27+g8HfCl+BIbV29vrwF4ZtFirt879u96/96PdnLzP87j971/AqB/wNAfh/6Ei2NxQ18884U2dtbAEe5e/y3+I/TwxJ+FiIjIce6xOdXM3f4EUKSR++XXfLEY1YiIiEgRTDjcv/L5+Xzl8/OL0RcREREpgiJ+k4yIiIhMBQp3ERERh1G4i4iIOIzCXURExGEU7iIiIg6jcBcREXEYhbuIiIjD5P2c+2NzqkvZDxERERmjfN8smzfcx/NVtCIiIlJ+mpYXERFxGIW7iIiIwyjcRUREHEbhLiIi4jAKdxEREYdRuIuIiDiMwl1ERMRhFO4iIiIOo3AXERFxGIW7iIiIwyjcRUREHEbhLiIi4jAKdxEREYdRuIuIiDiMwl1ERMRhFO4iIiIOo3AXERFxGIW7iIiIwyjcRUREHEbhLiIi4jAKdxEREYdRuIuIiDiMwl1ERMRhFO4iIiIOo3AXERFxGIW7iIiIwyjcRUREHEbhLiIi4jAKdxEREYdRuIuIiDiMwl1ERMRhFO4iIiIOo3AXERFxGIW7iIiIwyjcRUREHEbhLiIi4jAKdxEREYdRuIuIiDiMwl1ERMRhFO4iIiIOc0K5OyCl0d7eXu4uyDgtWbKk3F0QkQqjcD+OTPWQcLlcGGPK3Y0pRW/KRGQ8NC0vIiLiMAp3ERERh5nC4R4j4HPhcrlw+QLEyt0dqRjhBheuhvCUrU9EZLKVLNzH/AsyvIGmiJ+QMZiuRtyT17Vxsr35sB1jeYqxgE+hUXRhOoPgX1gzResTEZl8U3jkDnhn4Cl3HwrwtkQxxiSPkJ9grQtfQPMM4xZrpbrAm6VwQ/JadWuO1zncSdAXYHU6i0eqL9yQ9cZsNPXFWqvzv5kboa2Ryo1Yp4jIOJQp3JOj3oZwmIbBX2o+0pkYC/hw1QYh0oTHZR/xDx0tN5D5PRimweUjEGiwXYsR8PkIxOztpM8Pb7dwGwXUtGFCfiJNGzJlYgF8w35xJ9vwNEUgWIvLZXtDkPP+40UNDZ5GCERtb5aqycrccAO1B/34fblrCHcG8dVfk5rpCY9QX5iG2oMEoqk3ZtEANN7A0HzPqi/Wyg0d9URTb+aiAR/B2vT/HyO0NVK5EesUERmn3t5e09vba358uc9MppAfgz+UehQ1LV4MeE1LNNd1Y0zIb/C2mOjgiVQZ2z3RFq8Bv0meCRk/2B7b20mfSz/GpKuJtniz2hnaj6HXM5J1eVuGXkn2I1lFyPhtZYfWFW3xZj/nAvdPxOOPP16EWiYXNW3Dfn4BX+ZnlXmc/K8vkOu195lA5oeZv75owPiyrg0pm/ecTTRgfOnrI7U1UrkC1yrh5yYiU8OPL/eZdKaXdVreH+qiMbWYXrPQDwe682+ci+2gI+In1JZZ+3Q33oWfA3TbCvlDbQxdHc2cc7Og3gv+EOlq3NNnQeQQUYBYgHVBLy2rh7QR6WDHuGbaa2iz7RdwL6jHm26rKPc7i/vCmeD7yLClmIMvJl/8WOsNNM7M/OyGCXcS9NVzTeoFjL14MH997pV8wx+kNjWT01pdy8HAo6x0569vmOhv6WImF7oLtDVCuTFdExEZpcr5EpvoISLDTnqY4Y1wKArF23EXocnjoinrnJeWcdYWC/iS0++D/EW939ncXDgz9c9YKzc0ziRkaiDnW8AYrfcE8dVHR/hfwVYfUNNmCOGi1hUEX4BoVrIXqi91PRAld4+y28pXbvTXRERGb2pvqLPzzMA77GSUQxEvM4q66y61Qz/ryMwwFBTuJIifhTVAuAFP06xMfdGWHM/BXnaM9ztejBcPAkRpvaGRmTlmZTK3PkVHl4/6vMNse32Q3KPhonNhaq27vgOPy7a+X6C+cIOHRgI8ujJfe/a2RleucJ0iIqNTOeHuXkC9N0itbYdZLLCOoLeeBcX6XZhqY914d7vHAvhqg3hbVucMofCGpuGzDyMsReS838FiLx6Ert8OW4aYeSH8tguCtemNhh4au6Cr0YOrupUYEHuqgy7/N7Km1d0XzsxTn5tY6z1Zu+DdKx8l4Oui46nUEkCO+tLCDS5qDwaI7lmZWUIZoa2Ryo3mmojIWFVOuOOmsSuEP7W73OVy4emoJ1rUz8C7aeyKUt/hyf78+ghfohNpst3r6aA+auga3EjQRsgfpDZ1fd2MlqxJ9uSaevITAb5ArOD9jhfuxE+QzvT7t/AGGrv8LKypoS1rJiVKwAe+QBSzZyVuYjzV0TX8s+g1C/PUlyOMY0/R0ZUO4zz1EaO1Ok8Ij9DWiOVGvCYiMk6l2i0v5VUJu66B1G7x5CcayLtTfchu+WE73+235q8v5E+fTx4F6wv5s+4fPAY/epGnrZHKFaizEn5uIjI12HfLu3p7ew3AM4sWc/3ePaV7VyEl1d7e7ti/Chdrrcbz229g8m6jL299E1EJPzcRmRoem1PN3O1PABU1LS+SS74p9KlSn4hI6VXOR+FEcnKzck8x/wZ8sesTESk9jdxFREQcRuEuU8Z41ttFRGQ4hbuIiIjDaM39ONLe3l7uLoiISAko3I8T+jiVyMS8fSzBL3/9Jw7/dz9vHR0od3ekgvz1KSfyvnefyN9ecjrvfedJJWlT4S4iUsDbxxL889M9XPThd3Nh1Xs47ZRp5e6SVJC3jyZ4860+frbnj3y6+sySBLzCXUSkgF/++k985Lx3U3XmaQBY2vspY3DKydM45eRTOenEaez61R9Z8smqSW9T4S4iUsDvDh9hxrnvwSjVZQLe+86TeOPPfSVpS+EuIlJAf9wwbdo7NGKXiqFwFxEZBcsqdw9ERk/hLiIyChq1SyVRuB8n9Bl3qVRT5WOc+gJFqSQK9+PIkgWfK3cXRMakfcfj5e7CIEvpLhVE4S4iMgqVu+a+l0duaOZXQ09Xr+UH/jll6E8Pu+5aTufvr2LFo7dxSRl6cDxQuIuIjEIlr7kbgHOX8c27/56zbOfL9ZzSzRoDFfueaYpzZriHG3Ctm0G0qxF3ufsiIo5gVWy6m2SYmuRzsIfpG099mXt+8jLnfPZhbr+mCl7YwC0bn848poefN9/ET/+QLnE+n1n/fa4+O/Xw9Q7uu3MLr6UeXnbLTpa+P3XOt4aNN82x1XE+n1l/J/wgXd/TtN34NHxwGd9YW89ZqbbTBvuQaoPPLuMDP9nCc1yFf8tqZk3mS+YAJfqrcDECPhcuV/bREM6+K9zgwuULECtNp4YLN4zYPxE5fhlTuUe+53Bm7VIuA177STv7zV62bkyG7Y21VRjTw+7mm/jpH67ii5t38uDmnXzR+zI/vXMD+w0Ys5etd27htQ8u4+ubd/Lg5jXQ2cHrg8NyW1uD7VfxyTUP8+kPAqTqXVPPmYc7uK/zQ6l6kteTfcr0/7WfbIFbdvLg5tXMnAKv6UR/FpOtpH/y1dsSxRiTPEJ+grUufIFMlNe0GUy5RtuxAL7aIP5Qqn8mBOvK+EZjsr30VXzveh6X7WjYbb/hYgJXDT0H7G7FddVXB1+X8K3P47q1bvBy+NZkXb6HLs4qFnuoM3VfHQ1D2s0crdjfT6XrGtaHPNL3D39Oyecy9Fq6j9nPIde92f0ab/+kslmmcg8D8IctfGv5fG5dPp9bl3+Z3YfBMnO4/itXAU/zT8vX8Bzw8WvrOcOAdbiLfX8AvFdwUaqeiz6WvPeFF8B64VmeA865zJe838zh+uZk2aGvWSbccz+2zqpndaqsZao4qwrgFQ4fti0dfHAZV19S/tdyokeplO/vude0YUJ+Ik0bBn9xhhtcuOzD5VgA32hG0ln3+Qh0j3R9pBG5lxmewQ7SlvVGY+jsQ4PtF36MgM9HIBamIeu6vYyPgP2dQt4+Jcs0hO11jbbsKO1uxXXpFdS/8HHMX1LHC+0cWFycoPJe1kPkjuU5AxG20WZr00sPLYP9WEnN4H11dD7Sg//GHoI76nLWlLPte5dlntNfPk7bpzLX/E98POta1837R1VP6MZqam8d2ofx9U8ql2VV7mEAqpZxe3An3w3u5LvB7zP3zNT1i6/gY+knWbWMT16cOt/7u+R0e2QNjTfNTx7fS06bGwtef+0VAM4+uyp3e8b2OBVqZshje5n9D88fbOfhyJD7Ac45l/dNgddyokeplC/cAWoW4idIZ84UCNOwFLamRvrRFi/BnCPpGIGlTTA4K3AXh5qCY6/HvYB6b4Qmz5AgTdfS4KFpVmhw5iHacoDarCWECE2eThYagzFRWrxBal0eDt2Vvh+alqbvL9ynYO06ZkST10P+CE0bBt8CjfJ1yaeOhsXV+J9YSOMFttMXfIet9/YQ/PZXJz5bUbeVlstyBeIY7P4EwcueZfXKZ/E+8ok8bxRKo2bBHvjNh7NflynUPymNco/4ijFaNDmuvRHeyn+mb+jZwtZwT1a5Dyx6mAfadmYdSy7JjL4PH+7J216hkXumD19my1742JeS9d84J/t+J/wMjp+Re0HZI2f3gnq8kUNEh94W20FHxM9dje5MuZB/7PXgprErFaSeIaPlWIB1QS8tqzPjSnfjXfgjHeyw/cb3h9pSI083C+q94A/Rlirinj4LBtst3Cd/qIv0U6pZ6IcD3alwGe3zyeOlD3OAHmZcMPySe/6zeJ+7gh0vjbayfGI0BtvxPrKUwDjrCu+oxlv3c9wX/Jz6y6pZN2Sav3QuJvDtarjolazloqnTPymVcofCRI6hYTp4vN7Bj7a/DFXLWL1pDbOB3u3tHDBgXXJF9uMhx+kf/Ts+APQ+18UbBiyzl8fXdfDGWedyNsDeZ5Plft1OqCfZvgEsU8UZ5wC8whuvp+oD4HzOPDtZz/69tvvTP4Ap8Doq3IskFvBlpsE9TURy3RQ9lPv8WOtJqWmzjbSzRvHp0E8ftQRHqKeQsfSpmGV56Vwi/I7pOcK9qC74DnfdWEWTfxwzAS99lXWP9FA/fz+wnwV1PUS2XT2qeiJ3bMm7Vh5cXHgdfXg9W+ioW4Z5cFtR+ieVy7JMxR4GoGcLG1bM57b08cMIB0Nb6AUuXXAdp1uXc+XC84GnefSHESzrcuq/38ylPM2j9nIr7uegZbDOuI5b13yB9w/Wu5Uzb0rWU79ibrKeFfO57WfnsnTh+QAYk+zPjJov8H5eJtw8n9vu+Resqz/PpenHK9ZgLp+bud+k+k/5X8diHKVS3o/ChTsJ4idUk+taA56mWYRMV3I0HAvg8xwafp9nBl5ynB9rPUO4G7fS0uHhUBTwAPgJmTZydXXMxtmnCZcFuOD3eLmC7pegZpIDvubB9fjfdSdLH/o5W8dQLrbzCiKXPcvWVP/c85/Fe8cV7HjpOzRekNyc57kj/feQ9xCyrdV7712Wdy3d/0T2GvxIBuvZ3Ypr8XLCN2faKNQ/caZSjrqKaw7XPRTmupzXwqxfmvyXZeD0qx9i/dWZx3nLpkfbZ9Rzy0P1WZcsA8xazfqHVtvO1mfXO7ScYVg716X7Rebeyv0ZlF75Ru6p3eneltWjCszwhjwjVPd0ZhFkXXqInap3rPXEAr6snfuEN9AUSW2wcy+g3mtro8jyPrfJKHvBK8yiikM5psvTobXAFlAHokN2vUc/NIbGttH2xB4idyxnx6jLXMyObVXw3BI86VH2pUuIUEXHzmRf3DcvtG2MW1mcN1z5fGozLVnT7oX7J85U7o1YOpxxlEpJwz3S5LFNJ3dQHzV0Neb54FtNGyF/kNrU/etmtODPfSNtyTn0VL2HuCvagneM9bgbt1LfYetf7QFaoul1bzeNXdHs664JfCZ/1M+tyGUB2Mbqe3sILu7MXg/f3Yrnjir8X/tOam05Nd2cteu9jg1Z94xCKhybBkfaBexeTtNz9h30ySN670g78CfTfhq/tifT9pTrn5TKZK/F6jg+jlIp0bR8crNaY4G7atoMZujjNtuJfBW4G+ky2RdrusZaT6E+jnQ9eS27S11Zz4WatqwvMMjfp+F1UdOGqbE/HOXrkof75oVE6cRz6fM0DbkWXNzKwtRoePC+dz0/eN177zK6Rjm1nbSfxmA7HZcuGdUMQ3hHNdy4ftj0tvvmrfjvuJPO3VAzpvYzgoufz9onMdIUfpZP/Tt+km0zif2Tqa2Uv5hFJsrV29trAJ5ZtJjr9+4pd39kkrS3t4/ur8LtbsW1uHpM69Mik6V9x+NT4k++Bv71JS7xfKDc3RAH+K9oL43XTc7mnMfmVDN3+xNAuTfUydTzqZWYv5S7EyJTT0Ijd6kgCncRkQKmuVzEE4Z3uFzl7orIqCjcRUQKOOM9J3H0WD+nnnxSubsiFWwgnuC0U04sSVsKdxGRAv7m4tPZ/dz/MG3auzlh2pT+7i+ZoizL8Kc//4UrP3p6SdpTuIuIFFB1xinMnf1ewr96g77+En5YWRzjxBNcXDX7TNxVp5WkPYX7caR9x+Pl7oJIxTrv7FP50rUfLnc3REZF4X6cmAofJxIRkdLQ4pGIiIjDKNxFREQcRuEuIiLiMAp3ERERh1G4i4iIOIzCXURExGEU7iIiIg6jcBcREXEYhbuIiIjDKNxFREQcRuEuIiLiMAp3ERERh1G4i4iIOIzCXURExGEU7iIiIg6jcBcREXEYhbuIiIjDKNxFREQcRuEuIiLiMAp3ERERh1G4i4iIOIzCXURExGEU7iIiIg6jcBcREXEYhbuIiIjDKNxFREQcRuEuIiLiMAp3ERERh1G4i4iIOIzCXURExGEU7iIiIg6jcBcREXEYhbuIiIjDKNxFREQc5gT7g8fmVJerHyIiIlIkrt7eXlPuToiIiEjx/D+ra5tDbQ8xfgAAAABJRU5ErkJggg==)
--------- Código do formulário -------------------------------
Option ExplicitOption Compare DatabaseDim cnn As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim ssql As String-------------------------------------------------------------------------------------------------------------------------------------------
Private Sub cmdExecutar_Click()On Error GoTo trata_erro '-- conexão com SQL Server With cnn If .State = 0 Then .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _ "Persist Security Info=False;" & _ "Initial Catalog=NOME_DO_SEU_BANCO_DE_DADOS;" & _ "Data Source=NOME_SERVIDOR" .Open .CursorLocation = adUseClient End If End With '-- Instrução SQL da função que carrega o nome do dia da semana, ' passando como parametro a data ssql = "SELECT dbo.FDIA_SEMANA_EXT ('" & Format(Me.txtData, "yyyy-mm-dd") & "')" '-- Abre recordset com resultado da função rst.Open ssql, cnn, adOpenKeyset, adLockReadOnly '-- Campo recebe o valor do resultado do recordset Me.txtDiaSemana = rst(0) cnn.Close Set cnn = Nothing Exit Subtrata_erro: MsgBox "Erro gerado: " & Err.Number & " - " & Err.Description & "", vbCritical, "Erro" If cnn.State = 1 Then cnn.Close: Set cnn = Nothing Exit SubEnd SubExemplo de função SQL Server para carregar o dia da semana - Código:
CREATE FUNCTION FDIA_SEMANA_EXT (@DATA DATETIME) RETURNS VARCHAR (20) AS
BEGIN
DECLARE @DIA INT,
@DIA_EXT VARCHAR(20)
SELECT @DIA = (DATEPART(DW,@DATA ))
IF @DIA=1 SET @DIA_EXT ='DOMINGO'
IF @DIA=2 SET @DIA_EXT ='SEGUNDA-FEIRA'
IF @DIA=3 SET @DIA_EXT ='TERÇA-FEIRA'
IF @DIA=4 SET @DIA_EXT ='QUARTA-FEIRA'
IF @DIA=5 SET @DIA_EXT ='QUINTA-FEIRA'
IF @DIA=6 SET @DIA_EXT ='SEXTA-FEIRA'
IF @DIA=7 SET @DIA_EXT ='SÁBADO'
RETURN @DIA_EXT
END
No VBA, vá no menu Ferramentas >> Referências, e marque a referencia "Microsoft ActiveX Data Objects 2.8 Library"
PROCEDIMENTOS:
1 - Copie e Cole o script da function no SQL e execute.2 - Altere o nome do banco de dados e do servidor no código da conexão do formulário.Utilize este exemplo para executar as funções desejadas para o teu sistema.Espero ter ajudadoSegue exemplo.Att,Alexandre Fim- Anexos
ExecFunctionSQL.zip - Você não tem permissão para fazer download dos arquivos anexados.
- (37 Kb) Baixado 11 vez(es)