Hack sage200C (ini) Utilities

Un lugar dónde compartir funciones, procedimientos y aplicaciones
AngelMiguel
Nivel 2
Mensajes: 30
Registrado: Mar Nov 21, 2017 4:34 pm

Hack sage200C (ini) Utilities

Mensajepor AngelMiguel » Mar Mar 02, 2021 10:45 pm

02-03-2021

1.- Import file CSV "Hacienda Publica" IVA to table

Name: Mdl_ImportCsv

Option Compare Database
Option Explicit

Public Function TableExists(sName As String) As Boolean
TableExists = DCount("*", "MsysObjects", "[Name]= '" & sName & "'")
End Function


Private Sub importameDatosIvaPeriodo()
Dim i As Integer
Dim objStream As Variant
Dim line As String
Dim fso As Object
Dim strSql As String
Dim NaMeTable As String
Dim NombreCampo As String
Dim cadena As String
Dim arr As Variant
Dim SArchivo As String
Dim rst As DAO.Recordset

If TableExists("Tbl_RecibidasIva") Then DoCmd.DeleteObject acTable, ("Tbl_RecibidasIva")

SArchivo = CurrentProject.Path & "\Exportacion_RecibidasZIP.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(SArchivo) Then
Set objStream = fso.OpenTextFile(SArchivo, 1, False, 0)
End If

NaMeTable = "Tbl_RecibidasIva"

line = objStream.ReadLine
arr = Split(line, ";")
strSql = "CREATE TABLE " & NaMeTable & " ( "

For i = 0 To UBound(arr) - 2
cadena = Nz(Left(Trim(arr(i)), 45), i)
cadena = Replace(cadena, ".", " ", , , vbTextCompare)
NombreCampo = cadena
Select Case i
Case 6 To 90
strSql = strSql & "[" & NombreCampo & "]" & " Double, "
Case Else
strSql = strSql & "[" & NombreCampo & "]" & " TEXT, "
End Select
NombreCampo = ""
Dim sCadena
Next

strSql = strSql & "[" & Nz(Left(Trim(arr(i)), 20), i) & "]" & " " & " TEXT "
strSql = strSql & ")"
DoCmd.SetWarnings (False)
CurrentDb.Execute strSql
DoCmd.SetWarnings (True)

strSql = "": cadena = "": arr = ""

Set rst = CurrentDb.OpenRecordset("Select * from " & NaMeTable)
While Not objStream.AtEndOfStream
line = objStream.ReadLine
arr = Split(line, ";")
With rst
.AddNew
For i = 0 To UBound(arr) - 2
Select Case i
Case 6 To 90
If Len(arr(i)) = 0 Then
cadena = 0
Else
cadena = Nz(Left(Trim(arr(i)), 45), 0)
rst(i) = IIf(Len(cadena) = 0, 0, Replace(cadena, ".", ","))
End If
Case Else
cadena = Nz(Left(Trim(arr(i)), 45), "")
rst(i) = cadena
End Select
Next
.Update
End With
Wend
Set rst = Nothing
Access.RefreshDatabaseWindow
Set fso = Nothing
End Sub


2.- Import tables Sql Sage200c

Private Sub CmdImportIva_Click()

If TableExists("Movimientos") Then DoCmd.DeleteObject acTable, ("Movimientos")
If TableExists("MovimientosIva") Then DoCmd.DeleteObject acTable, ("MovimientosIva")
If TableExists("MovimientosFacturas") Then DoCmd.DeleteObject acTable, ("MovimientosFacturas")

Dim SUid As String: SUid = "NameUsuarioSage200c"
Dim Sclave As String: Sclave = "ClaveSage200c"
Dim Sdatabase As String: Sdatabase = "NameDatabase"
Dim Sserver As String: Sserver = "NameServidor"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientos", "Movimientos"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientosFacturas", "MovimientosFacturas"
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;Driver=SQL Server Native Client 11.0;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No", acTable, "movimientosIva", "MovimientosIva"

End Sub


...continuará

AngelMiguel
Nivel 2
Mensajes: 30
Registrado: Mar Nov 21, 2017 4:34 pm

Re: Hack sage200C (ini) Utilities

Mensajepor AngelMiguel » Jue Jul 21, 2022 10:35 am

Consultar todos los registros de una base de datos Sage200c en un servidor sql , en modo desconectado.

En este caso consultaremos todos los registros de la tabla denominada LineasAlbaranProveedor, para ello nuestro form no tendrá origen de registro alguno

Private Sub Comando111_Click()
On Error GoTo Comando111_Click_Error

Dim SUid As String
Dim Sclave As String
Dim Sdatabase As String
Dim Sserver As String

Dim cn As ADODB.Connection
Dim rscn As ADODB.Recordset

Dim TxtNumberRecord As integer '' usado como control , podeis eliminarlo
Dim AccessConnect As String

Dim Strsql as string
Dim frm As Access.Form

Set frm = Application.Forms(Me.Name)


SUid = "Usuario"
Sclave = "ClaveUsuario"
Sdatabase = "NombreBaseDeDatos"
Sserver = "NombreServidor"

[color=#008000] '' Construimos nuestro select[/color]
strsql = ""
strsql = strsql & " SELECT LineasAlbaranProveedor.* "
strsql = strsql & " FROM LineasAlbaranProveedor "

'' Ahora establecemos los parametros de la conexión al servidor
AccessConnect = "Driver=SQL Server;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No"

Set cn = New ADODB.Connection
With cn
cn.ConnectionString = AccessConnect
cn.Open
End With

Set rscn = New ADODB.Recordset
With rscn
Set rscn.ActiveConnection = cn
rscn.Source = strsql
rscn.LockType = adLockOptimistic
rscn.CursorType = adOpenStatic
rscn.CursorLocation = adUseClient
rscn.Open
TxtNumberRecord = rscn.RecordCount '' podemos obviarlo
Set frm.Recordset = rscn '' conectamos al formulario
frm.Refresh
End With
Set rscn = Nothing
Set cn = Nothing
Set frm = Nothing
strsql = ""

On Error GoTo 0
Exit Sub

Comando111_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Comando111_Click, line " & Erl & "."

End Sub

Comentario: Habitualmente tengo una base de datos accdb en la que he vinculado la totalidad de las tablas del servisor SQL que me permite explorar cuales son las tablas que necesito para trabajar y estudio sus relaciones.

AngelMiguel
Nivel 2
Mensajes: 30
Registrado: Mar Nov 21, 2017 4:34 pm

Re: Hack sage200C (ini) Utilities

Mensajepor AngelMiguel » Vie Jul 22, 2022 1:10 pm

Obtener los nombres de columnas de una tabla de una base de datos Sage200c en un servidor sql , en modo desconectado.

Public Sub ObtenerNombresDeColumnas()
On Error GoTo ObtenerNombresDeColumnas_Error

Dim SUid As String
Dim Sclave As String
Dim Sdatabase As String
Dim Sserver As String

Dim cn As ADODB.Connection
Dim rscn As ADODB.Recordset
Dim AccessConnect As String

Dim sta As String
Dim Field As ADODB.Field


SUid = "Usuario"
Sclave = "ClaveUsuario"
Sdatabase = "NombreBaseDeDatos"
Sserver = "NombreServidor"

strsql = ""
strsql = strsql & " SELECT LineasAlbaranProveedor.* "
strsql = strsql & " FROM LineasAlbaranProveedor "


AccessConnect = "Driver=SQL Server;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No"

Set cn = New ADODB.Connection
With cn
cn.ConnectionString = AccessConnect
cn.Open
End With

Set rscn = New ADODB.Recordset
With rscn
Set rscn.ActiveConnection = cn
rscn.Source = strsql
rscn.LockType = adLockOptimistic
rscn.CursorType = adOpenStatic
rscn.CursorLocation = adUseClient 'adUseServer
rscn.Open
End With

With rscn
For Each Field In rscn.Fields
sta = sta & Field.Name & ";"
Next
End With

Msgbox sta

Set rscn = Nothing
Set cn = Nothing

strsql = ""

On Error GoTo 0
Exit Sub
ObtenerNombresDeColumnas_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ObtenerNombresDeColumnas, line " & Erl & "."
End Sub
Última edición por AngelMiguel el Vie Jul 22, 2022 1:20 pm, editado 1 vez en total.

AngelMiguel
Nivel 2
Mensajes: 30
Registrado: Mar Nov 21, 2017 4:34 pm

Re: Hack sage200C (ini) Utilities

Mensajepor AngelMiguel » Vie Jul 22, 2022 1:18 pm

Actualizar uno o varios, en este caso varios, campos de una base de datos Sage200c en un servidor sql , en modo desconectado.

No recomiendo toquetear las tablas y campos si se desconoce las relaciones entre las mismas.

Private Sub ActualizaCamposTabla()
On Error GoTo ActualizaCamposTabla_Error
'update
Dim SUid As String
Dim Sclave As String
Dim Sdatabase As String
Dim Sserver As String
Dim cn As ADODB.Connection
Dim AccessConnect As String

SUid = "Usuario"
Sclave = "ClaveUsuario"
Sdatabase = "NombreBaseDeDatos"
Sserver = "NombreServidor"

strsql = ""
strsql = strsql & " UPDATE LineasAlbaranProveedor "
strsql = strsql & " SET AnaCapitulo ='" & "Dxxxx" & "',"
strsql = strsql & " AnaLote ='" & "D1xxxxxxx" & "'"
strsql = strsql & " WHERE NumeroAlbaran=1684 "
strsql = strsql & " AND LineasAlbaranProveedor.Orden=5"
strsql = strsql & " AND LineasAlbaranProveedor.CodigoProyecto='" & "DDDD" & "'"

AccessConnect = "Driver=SQL Server;Uid=" & SUid & ";Pwd=" & Sclave & ";LANGUAGE=Español;Server=" & Sserver & ";Database=" & Sdatabase & ";Trusted_Connection=No"

Set cn = New ADODB.Connection
With cn
cn.ConnectionString = AccessConnect
cn.ConnectionTimeout = 10
cn.Open
cn.Execute strsql, , ADODB.ExecuteOptionEnum.adExecuteNoRecords
End With
Set cn = Nothing
strsql = ""


On Error GoTo 0
Exit Sub

ActualizaCamposTabla_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ActualizaCamposTabla, line " & Erl & "."

End Sub


Volver a “Almacén Accesiano”

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 0 invitados